CODEWITHSUNDEEP
mysql
Tim
Tim

Reputation: 3131

Displaying related results with group concat

I'm trying to get all events for the person corresponding to feed X AND return the other people attending that event... in a single query.

 events (TABLE)
 - id int (10)
 - name varchar (20)

 events_people (TABLE)
 - id int (10)
 - event_id int (10)
 - person_id int (10)

 feed (TABLE)
 - id int (10)
 - feed_id varchar (20)
 - person_id int (10)

 people (TABLE)
 - id int (10)
 - first_name varchar (20)

I can get this far with this simple query:

SELECT events.id AS event_id, GROUP_CONCAT(DISTINCT(people.first_name)) AS attending
FROM events
LEFT JOIN events_people ON events.id = events_people.event_id
LEFT JOIN people ON events_people.person_id = people.id
LEFT JOIN feed ON events_people.person_id = feed.person_id
GROUP BY events.id

but as soon as I add a conditional like WHERE feed.feed_id IN ('SHFDskdf'), it filters all users who are not related to feed X and therefore breaks the group concatenation.

I'm sure this isn't rocket science, but after spending a couple of hours on this I sure feel like it is.

SQL Fiddle: http://sqlfiddle.com/#!2/3143c/2/0

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Just move the condition to the on clause:

SELECT events.id AS event_id, GROUP_CONCAT(DISTINCT(people.first_name)) AS attending
FROM events LEFT JOIN
     events_people
     ON events.id = events_people.event_id LEFT JOIN
     people
     ON events_people.person_id = people.id LEFT JOIN
     feed
     ON events_people.person_id = feed.person_id AND feed.feed_id IN ('SHFDskdf')
GROUP BY events.id;

EDIT:

The issue is that the filter has no impact on people.first_name. You can fix this with a case statement:

SELECT e.id AS event_id,
       GROUP_CONCAT(DISTINCT case when f.person_id is not null then p.first_name end) AS attending
FROM events e LEFT JOIN
     events_people ep
     ON e.id = ep.event_id LEFT JOIN
     people p
     ON ep.person_id = p.id LEFT JOIN
     feed f
     ON ep.person_id = f.person_id AND f.feed_id IN ('SHFDskdf')
GROUP BY e.id;

This should do what you want.

EDIT II:

Now I think I know what you want:

SELECT e.id AS event_id,
       GROUP_CONCAT(DISTINCT p.first_name) AS attending
FROM events e LEFT JOIN
     events_people ep
     ON e.id = ep.event_id LEFT JOIN
     people p
     ON ep.person_id = p.id LEFT JOIN
     feed f
     ON ep.person_id = f.person_id 
GROUP BY e.id
HAVING SUM(f.feed_id IN ('SHFDskdf')) > 0;

Upvotes: 2

Related Questions