mysql - 使用组连接显示相关结果

Question

我正在尝试获取与 Feed X 对应的人员的所有事件，并在单个查询中返回参加该事件的其他人员...。

 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)

通过这个简单的查询我可以得到这么远:

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

但一旦我添加像 WHERE feed.feed_id IN ('SHFDskdf') 这样的条件，它就会过滤与 Feed X 不相关的所有用户，从而破坏组串联。

我确信这不是火箭科学，但在花了几个小时之后，我确实觉得它是。

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

Answer 1

只需将条件移至 on 子句即可:

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;

编辑:

问题是过滤器对 people.first_name 没有影响。您可以使用 case 语句修复此问题:

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;

这应该可以满足您的要求。

编辑二:

现在我想我知道你想要什么:

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;