Many tables with many points with a vault

Question

I currently have two tables, similar to users and programs , which are linked through many-to-many relationships using the link table.

 mysql> select * from users; +----+----------+ | id | name | +----+----------+ | 1 | Jonathan | | 2 | Little | | 3 | Annie | | 4 | Bob | +----+----------+ 4 rows in set (0.00 sec) mysql> select * from programs; +----+----------------------+ | id | name | +----+----------------------+ | 1 | Microsoft Word | | 2 | Microsoft Excel | | 3 | Microsoft PowerPoint | +----+----------------------+ 3 rows in set (0.00 sec) mysql> select * from link; +---------+------------+ | user_id | program_id | +---------+------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 2 | | 3 | 1 | | 3 | 4 | +---------+------------+ 6 rows in set (0.00 sec) 

I understand how to join tables and return a result of this kind:

 mysql> select users.name, programs.name from linker -> join users on users.id = linker.user_id -> join programs on programs.id = linker.program_id; +----------+----------------------+ | name | name | +----------+----------------------+ | Jonathan | Microsoft Word | | Jonathan | Microsoft Excel | | Jonathan | Microsoft PowerPoint | | Little | Microsoft Excel | | Annie | Microsoft Word | +----------+----------------------+ 

But what I'm really looking for is a bit more complicated:

 +----------+-----------------------------------------------------+ | name | name | +----------+-----------------------------------------------------+ | Jonathan | Microsoft Word,Microsoft Excel,Microsoft PowerPoint | | Little | Microsoft Excel | | Annie | Microsoft Word | +----------+-----------------------------------------------------+ 

I assume there is somewhere in the GROUP_CONCAT() file, but I cannot make the results look like this:

 mysql> select users.name, group_concat(programs.name) from linker -> join users on users.id = linker.user_id -> join programs on programs.id = linker.program_id; +----------+------------------------------------------------------------------------------------+ | name | group_concat(programs.name) | +----------+------------------------------------------------------------------------------------+ | Jonathan | Microsoft Word,Microsoft Excel,Microsoft PowerPoint,Microsoft Excel,Microsoft Word | +----------+------------------------------------------------------------------------------------+ 

Can someone point me in the right direction?

Answer 1

You need to specify DISTINCT , i.e.

 select users.name, group_concat( DISTINCT programs.name) 

See here docs here .

Try changing the query:

 SELECT users.name, group_concat(programs.name) from users LEFT JOIN linker on linker.user_id = users.id LEFT JOIN programs on linker.program_id = programs.id GROUP BY users.id 

This will give you null for any user without any programs related to them. To filter them, just add WHERE programs.id IS NOT NULL .

Answer 2

 SELECT users.name, group_concat(programs.name) from linker INNER JOIN users on linker.user_id = users.id INNER JOIN programs on linker.program_id = programs.id GROUP BY users.id;