GROUP CONCAT with Table Joins

Databases
1

Hi!

I have:

Table: location
id, name
1, ABC
2, DEF
3, GHI

table: user
id, username, name
1, john, John
2, peter, Peter
3, mary, Mary

table: reportusers
fkLocationId, fkUserId
2, 2
2, 3

I am trying to get this result:

ABC, “”
DEF, “Peter(peter), Mary (mary)”
GHI, “”

I have tried this SQL statement:

SELECT GROUP_CONCAT(user.name, ’ (‘, user.username ,’)’ SEPARATOR ', ') as names
FROM location LEFT JOIN reportusers
ON location.id = reportusers.fkLocationId
LEFT JOIN user
ON user.id = reportusers.fkUserId
GROUP BY location.id

but it only returns DEF, Peter(peter), Mary(mary) but not ABC and GHI.

It is ok if I do not use GROUP_CONCAT.

How do I get the desired result?

Please help.

Thanks.

2

no, it doesn’t return DEF either

DEF comes from location.name, and location.name is not included in your SELECT clause at all

no, it is not okay, you have to use it if you want multiple users for each location

i showed you how to use GROUP_CONCAT in your previous thread

3

Hi!

My apologise. My SQL as follows:

SELECT location.name, GROUP_CONCAT(user.name, ’ (‘, user.username ,’)’ SEPARATOR ', ') as names
FROM location LEFT JOIN reportusers
ON location.id = reportusers.fkLocationId
LEFT JOIN user
ON user.id = reportusers.fkUserId
GROUP BY location.id

Thanks for pointing out.

4

i believe your GROUP_CONCAT is still wrong

please refer to my reply in your previous thread