MySql Query Join 3 tables and grouping 1

Question

So I have the below database structure

TABLES ------- Columns
person:       id, name, salary, address
group:        id, name    
person_group: person_id, groud_id

So here is my query which is used to get all persons along with the groups they are associated with

SELECT p.id, p.name, 
group_concat(g.name) as groups 
FROM person_group pg, group g, person p
WHERE pg.group_id = g.id AND pg.novel_id = n.id
GROUP BY ng.person_id

So this query gives me data like

 id     name    groups
 2345   John    Admin, SuperAdmin, RedHat

But the problem is: if that person doesn't belong to any group, the query doesn't return that person!

Any would be appreciated!

Answer 1

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

That is exactly your problem here. When you are writing more than one table in the FROM clause, you should be thinking "what type of JOIN do I need". If you had that thought, you would immediate realize that you need an outer join to do what you want:

SELECT p.id, p.name, group_concat(g.name) as groups 
FROM person p LEFT JOIN
     person_group pg
     ON pg.person_id = p.id LEFT JOIN
     group g
     ON pg.group_id = g.id 
GROUP BY p.id, p.name;

If you don't know what an outer join is, then that is all the more reason to use the proper, explicit syntax, so you can learn.