CODEWITHSUNDEEP
mysqlsql
johndavies112233
johndavies112233

Reputation: 11

GROUP_CONCAT function with multiple tables

I have never used the GROUP_CONCAT function before and in the examples I find online, I cannot seem to find one which spans multiple tables like I am trying to do.  My real case has table names which will make little sense so I am making up an example which will be easier to understand

Tables are

Table: vegetables
vegtable_id vegtable_name

Table: favorite_vegtables
persons_id
vegtable_id 

Table: people
persons_id
persons_name
persons_phone
persons_country_id

Table: country
country_id
country_name
population
flag_icon

Now Ideally I would like to get back a result set which looks like this:

Name     Country    FavoriteVegtables
John     France     Carrots, Beans, Peas
Joe      NULL       Lettuce, Onions, Peas
Jack     Italy      NULL

You can see that the Favorite Vegetables essentially has a list of all the veggies the person likes so the would be a concat of values.

I would normally query the people and country tables by using something like this:

SELECT p.persons_name,c.country_name FROM people p
LEFT OUTER JOIN country c ON p.persons_country_id = c.country_id;

but I don't know how to then get the FavoriteVegtables column where it will join up the favorite_vegtables table with vegetables table and give me the concatenated results in the same row.

Any help would be greatly appreciated!

Upvotes: 0

Views: 564

Answers (1)

FanoFN
FanoFN

Reputation: 7124

Add another LEFT JOIN to the favorite_vegetable then vegetable and add GROUP_CONCAT(vegetable_name) in the SELECT:

SELECT p.persons_id, p.persons_name, c.country_name, 
       GROUP_CONCAT(v.vegetable_name) AS FavoriteVegetables
  FROM people p
   LEFT JOIN country c ON p.persons_country_id = c.country_id
   LEFT JOIN favorite_vegetables fv ON p.persons_id=fv.persons_id
   LEFT JOIN vegetables v ON fv.vegetable_id=v.vegetable_id
GROUP BY p.persons_id, p.persons_name, c.country_name
ORDER BY p.persons_id;

I've replaced your LEFT OUTER JOIN with just LEFT JOIN because there's no difference other than the first one is longer to write.

Demo fiddle

Upvotes: 2

Related Questions