我需要执行一个带有关系除法的查询
我有什么:2个表,第三个表用于多对多关系
=> 会议有多个参与者,参与者可以参加多个会议
我需要什么:获得至少具有给定参与者的会议
这里有一个SQL查询,它完成了这项工作:
SELECT
m.meeting_id,
m.name,
m.status
FROM meetings As m
INNER JOIN meetings_participants AS mp ON m.meeting_id = mp.meeting_id
WHERE m.status <> 'temporary'
AND mp.participant_id IN (1, 2)
GROUP BY m.meeting_id
HAVING COUNT(DISTINCT mp.participant_id) >= 2
SQL 小提琴 : http://sqlfiddle.com/#!9/8a331d/6/0
问题1:有没有办法选择我需要的东西,而没有"有计数......".part?
问题2:在第二个查询中,我需要与给定参与者的会议,该怎么做?
问题1:有没有办法选择我需要的东西,而没有'拥有 数...」.part?
是的,您可以使用多个 JOIN,但这不是一个很好的解决方案。因为您需要为每个mp.participant_id提供一个 JOIN...您拥有的查询更具可扩展性
查询
SELECT
m.meeting_id
, m.name
, m.status
FROM
meetings AS m
INNER JOIN meetings_participants AS mp1 ON m.meeting_id = mp1.meeting_id AND m.status <> 'temporary' AND mp1.participant_id = 1
INNER JOIN meetings_participants AS mp2 ON m.meeting_id = mp2.meeting_id AND m.status <> 'temporary' AND mp2.participant_id = 2
结果
| meeting_id | name | status |
|------------|-----------|-----------|
| 1 | a meeting | active |
| 5 | e meeting | cancelled |
演示 http://sqlfiddle.com/#!9/8a331d/54
问题2:在第二个查询中,我需要与确切的会议 给定的参与者
您需要首先为具有两条记录的每个meeting_participants找到计数
跟
SELECT
meeting_id
FROM
meetings_participants
GROUP BY
meeting_id
HAVING COUNT(*) = 2
并像这样在主查询中使用它。
查询
SELECT
meetings.*
FROM (
SELECT
meeting_id
FROM
meetings_participants
WHERE
participant_id IN(1, 2)
AND
meeting_id IN(
SELECT
meeting_id
FROM
meetings_participants
GROUP BY
meeting_id
HAVING COUNT(*) = 2
)
GROUP BY
meeting_id
HAVING COUNT(*) = 2
)
meetings_participants
INNER JOIN
meetings
ON
meetings_participants.meeting_id = meetings.meeting_id
结果
| meeting_id | name | status |
|------------|-----------|-----------|
| 5 | e meeting | cancelled |
演示 http://sqlfiddle.com/#!9/8a331d/46