Showing all joined results independent from where clause

Question

I have two tables (fruit, vitamin) and a many to many relation table (fruit_vitamin)

I'm listing all the fruits with their respective vitamins in a comma separated column using this query:

SELECT `fruit`.`name`,
GROUP_CONCAT(DISTINCT `vitamin`.`name` ORDER BY `vitamin`.`id` ASC SEPARATOR ', ') `vitamins`
FROM `fruit`
LEFT JOIN `fruit_vitamin` `fv` ON `fruit`.`id` = `fv`.`fruit_id`
LEFT JOIN `vitamin` ON `vitamin`.`id` = `fv`.`vitamin_id`
WHERE 1
GROUP BY `fruit`.`id` 
ORDER BY `fruit`.`name` ASC

here is the result:

now if I try to filter the results so I can show only the fruits with the vitamin B12 for example, adding the where clause:

WHERE `vitamin`.`id` = 4

the query show the correct fruits, but the vitamin concat result shows only the vitamin B12 although the fruits actually contain more vitamins.

what I'm doing wrong? how can I filter the results without affecting the GROUP_CONCAT?

Answer 1

Try this:

SELECT `fruit`.`name`,
GROUP_CONCAT(DISTINCT `vitamin`.`name` ORDER BY `vitamin`.`id` ASC SEPARATOR ', ') `vitamins`
FROM `fruit`
LEFT JOIN `fruit_vitamin` `fv` ON `fruit`.`id` = `fv`.`fruit_id`
LEFT JOIN `vitamin` ON `vitamin`.`id` = `fv`.`vitamin_id`
WHERE `fruit`.id IN (SELECT `fruit_id` FROM `fruit_vitamin` WHERE `vitamin_id` = 4)
GROUP BY `fruit`.`id` 
ORDER BY `fruit`.`name` ASC