MySQL GROUP_CONCAT + IN() = missing data :-(

Question

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?

Answer 1

you must use HAVING clause instead of WHERE:

SELECT box.boxID
     , box.color
     , GROUP_CONCAT(DISTINCT boxHas.has SEPARATOR ", ") AS contents
  FROM box
  LEFT JOIN boxHas 
    ON box.boxID=boxHas.boxID
 GROUP BY box.boxID
HAVING SUM(boxHas.has IN ('apple','pear')) >= 2
 ORDER BY box.boxID