Mysql Select Problem

Forum|Forum|17 years ago
December 20, 2008
3 replies
713 views

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

Advanced techniques

This topic has been closed for replies.

Correct answer -__cfSearching__-

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

T

ty554r

Participant

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

A

Anonymous

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

-

-__cfSearching__-

Inspiring

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

A

Anonymous

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__-Correct answer

Inspiring

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded