GROUP_CONCAT function with multiple tables

Question

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!

FanoFN · Accepted Answer · 2021-09-14 02:35:11Z

2

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

answered Sep 14, 2021 at 2:35

FanoFN

7,1442 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

johndavies112233 Over a year ago

Great! Many thanks for the info and also putting into a Demo Fiddle!

Collectives™ on Stack Overflow

GROUP_CONCAT function with multiple tables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related