多个sql表选择
多个sql表选择
提问于 2013-06-14 21:23:23
有客户和节日的餐桌。每个节日每年都会发生(像新年一样)。但并不是每个客户都会被邀请参加任何节日。
我需要得到女性客户，她们在festival1上被邀请了，但在festival2上没有被邀请。
Table "clients"
+-----+--------------+-----------+---------+-----+
| id | name | email | adress | sex |
+-----+--------------+-----------+---------+-----+
| 1 | Ivan | iva@ya.su | NY city | m |
| 2 | Michael | lad@ya.su | LA | m |
| 3 | Anna | al@ya.su | LA | w |
| ...
+-----+--------------+-----------+---------+-----+
Table festivals
+-----+------------+-------+
| id | name | date |
+-----+------------+-------+
| 1 | festival1 | 8-03 |
| 2 | festival2 | 23-02 |
| 3 | festival3 | 1-01 |
| ...
+-----+------------+-------+
Talbe "invitations"
+--------+----------+------+
| client | festival | year |
+--------+----------+------+
| 1 | 2 | 2013 |
| 3 | 1 | 2009 |
| ...
+--------+----------+我开始做一些类似这样的查询，但它需要更正：
SELECT name
FROM clients, festivals, invitations
WHERE clients.sex = w
AND festivals.name = festival1
AND clients.id = invitations.client
AND invitations.year = 2013回答 4
Stack Overflow用户
回答已采纳
发布于 2013-06-14 21:59:19
您可以使用NOT EXISTS从查询中删除结果：
SELECT *
FROM Clients
INNER JOIN Invitations
ON Invitations.Client = Clients.ID
INNER JOIN Festivals
ON Festivals.ID = Invitations.Festival
WHERE Festivals.Name = 'Festival1'
AND Clients.Sex = 'W'
AND Invitations.Year = 2013
AND NOT EXISTS
( SELECT 1
FROM Invitations i2
INNER JOIN Festivals f2
ON f2.ID = i2.Festival
WHERE i2.Client = Clients.ID
AND f2.Name = 'Festival2'
AND i2.Year = Invitations.Year
);Stack Overflow用户
发布于 2013-06-14 21:27:44
SELECT c.name
FROM clients c
INNER JOIN invitations i ON c.id = i.client
INNER JOIN festivals f ON f.id = i.festival
WHERE c.sex = 'w'
AND i.year = 2013
group by c.name
having sum(case when f.name='festival1' then 1 else 0 end) > 0
and sum(case when f.name='festival2' then 1 else 0 end) = 0Stack Overflow用户
发布于 2013-06-14 21:35:17
我会这样做：
SELECT c.Name
FROM clients c
INNER JOIN invitations i
ON i.client = c.id
AND i.year = 2013
INNER JOIN festivals f
ON i.festival = f.id
AND f.name = 'festival1'
WHERE
c.sex = 'w'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17109536
复制相关文章
相似问题
腾讯云开发者
