This content has been marked as final. Show 5 replies
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'
LEFT JOIN ( SELECT inventory.refid,inventory.subcode,
COUNT(inventory.refid) as totalstock
WHERE sold = 'No'
GROUP BY inventory.subcode ASC
inventory ON subgroups.subcode = inventory.subcode
WHERE subgroups.groupno = 'CPU'
> 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).
) as 'VALUE'
Thanks so much you are a genius. and have saved me alot of time and headaches.