Mysql Select Problem

[Guest]

Good day all.

Im having problems with selecting information from a mysql database.

This is what i want to do.

I have a table with a bunch of products and prices in them.

This is the query i use to access the info which works perfectly

"
SELECT CONCAT(groupno,"-",subcode) AS 'Key', CONCAT(groupno, " - ",subcode," - ",headertext," - ",description," -R",price) as 'VALUE'
FROM subgroups
WHERE

[-__cfSearching__-]

Is this the same question as here?
http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=7&threadid=1413661

[Guest]

Ive decided to post on this topic as it was the most complete one. Thanks on the tetorial site. It explained joins better than the mysql reference manual. so now i have a working query. but still with one problem.

I can now get all the descriptions etc and stock. but the VALUE field does not display if thwere are no records in my inventory table. EG if count (totalstock) is 0. the value does nt display is there anyway that if count is NULL. it makes it display 0 or something like that.

Here is my query
Thats all for your help:

SELECT CONCAT(subgroups.groupno,"-",subgroups.subcode) AS 'Key', CONCAT(subgroups.groupno, " - ",subgroups.subcode," : STOCK ",totalstock,"-",subgroups.headertext," - ",subgroups.description," -R",subgroups.price) as 'VALUE'
FROM subgroups
LEFT JOIN ( SELECT inventory.refid,inventory.subcode,
COUNT(inventory.refid) as totalstock
FROM inventory
WHERE sold = 'No'
GROUP BY inventory.subcode ASC
)
inventory ON subgroups.subcode = inventory.subcode
WHERE subgroups.groupno = 'CPU'

[-__cfSearching__- · Accepted solution]

ShapeShift wrote:
> but the VALUE field does not display if thwere are no records in my inventory table.

Yes. If there are no inventory records the "totalstock" value will be null. So when you try and concatenate it with the other columns the final string also becomes null. Try using the COALESCE function to return "0" instead. (Convert the column value to a varchar first if needed).

CONCAT(
...
COALESCE(totalstock, 0),
....
) as 'VALUE'

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce

[Guest]

Thanks so much you are a genius. and have saved me alot of time and headaches.

[ty554r]

Thanks a lot Jay! This thread is solved and should be closed by moderators.

__________________________________________________
Tiffany Jewelry
Tiffany Necklace
Tiffany Pendant
Tiffany Bracelet
Tiffany Earring
Tiffany Ring