Rod Rod - 6 months ago 9
SQL Question

SQL query displaying one column and multiple values

I have a couple of tables...one contains sites and another contains some users. There is a link binding specific users to specific sites. When I run my query:

select sites.site, users.fullname
from sites
inner join users
on sites.nameid = users.id;


I get the following output:

SITE | FULLNAME

site A | John Doe
site B | John Doe
site C | John Doe
site D | Roger Rabbit
site E | Roger Rabbit
site F | Batman


What I'm looking for is one FULLNAME with the list of sites below:

SITE | FULLNAME

site A | John Doe
site B
site C
site D | Roger Rabbit
site E
site F | Batman


I don't know if this is even possible or if I have to use something else to get the format I'm looking for.

Answer

This is not exactly what you want, but it might help:

SELECT users.fullname, GROUP_CONCAT(sites.site ORDER BY sites.site SEPARATOR ', ') 
FROM sites 
LEFT JOIN users ON sites.nameid = users.id
GROUP BY users.id;

This outputs:

John Doe     | site A, site B, site C
Roger Rabbit | site D, site E
Batman       | site F

To get exactly the result you want, I would do this in my PHP script.

Comments