MySQL GROUP_CONCAT + IN() = missing data :-(
- by Andrew Heath
Example:
Table: box
boxID color
01 red
02 blue
03 green
Table: boxHas
boxID has
01 apple
01 pear
01 grapes
01 banana
02 lime
02 apple
02 pear
03 chihuahua
03 nachos
03 baby crocodile
I want to query on the contents of each box, and return a table with each ID, color, and a column that concatenates the contents of each box, so I use:
SELECT box.boxID, box.color,
GROUP_CONCAT(DISTINCT boxHas.has SEPARATOR ", ") AS contents
FROM box
LEFT JOIN boxHas ON box.boxID=boxHas.boxID
WHERE boxHas.has IN ('apple','pear')
GROUP BY box.boxID
ORDER BY box.boxID
and I get the following table of results:
boxID color contents
01 red apple, pear
02 blue apple, pear
My question to you is: why isn't it listing ALL the has values in the contents column? Why is my WHERE statement also cropping my GROUP_CONCAT?
The table I thought I was going to get is:
boxID color contents
01 red apple, banana, grapes, pear
02 blue apple, lime, pear
Although I want to limit my boxID results based upon the WHERE statement, I do not want to limit the contents field for valid boxes. :-/
Help?
