COALESCE() function with join

Question

I have three tables,

comments: id, user_id, place_id, text
places: id, name
users: id, name

I'd like to show a list of all the places with a list of all the users who commented on that place.

McDonalds  Jill, Suzy, Bob
Walmart    Fred, Joe, Suzy, Larry
Library    Joe, Suzy
...

I am trying to use the coalesce function to achieve this but I am running into trouble. What am I doing wrong?

SELECT places.name, COALESCE(users.name+",")
FROM comments
JOIN places
ON comments.place_id = places.id
WHERE user_id = users.id
GROUP BY places.name

Thanks.

Which RDBMS please? `COALESCE` returns the first non-null value. Possibly you are thinking of MySql `GROUP_CONCAT`? — StuartLC, Feb 11 '14 at 04:49
Using SQL Server 2005 for Windows... for a class with outdated textbook — broinjc, Feb 11 '14 at 04:52
Have a look [here](http://stackoverflow.com/a/5981860/314291) at options on mimicking `GROUP_CONCAT` in sql server. e.g. using `FOR XML PATH` + `STUFF` — StuartLC, Feb 11 '14 at 04:55
Could you please provide sample data and possibly table creation script ? — Mahmad Khoja, Feb 11 '14 at 04:58

score 1 · Accepted Answer · answered Feb 11 '14 at 04:58

Try this !

Using Coalesce is not relevant here,try using STUFF with XML PATH('')

SELECT t1.name, 
STUFF(
(
SELECT ',' + [places.name] FROM comments
JOIN places
ON comments.place_id = places.id
WHERE user_id = users.id
GROUP BY places.name
for xml path('')
),1,1,'') as t1 from table <group by>

COALESCE() function with join

1 Answers1