使用子查询组合 2 个 mysql 查询
我想知道这里是否有人可以帮助解决我遇到的问题。我正在尝试使用子查询将 2 个 mysql 查询合并为一个查询。我目前有两个查询分别产生我想要的结果:
这是两个查询:
SELECT contact_id FROM contacts
WHERE acc_id = 1 AND email LIKE "paul%"
SELECT c0.contact_id
FROM contact_tags c0 INNER JOIN contact_tags c1
on c0.contact_id = c1.contact_id INNER JOIN contact_tags c2
on c1.contact_id = c2.contact_id where c0.tag_id = 1
AND c1.tag_id = 2 AND c2.tag_id = 3
这是表中的一些示例数据:
Contacts:
contact_id acc_id email
54 1 [email protected]
Tags:
id contact_id tag_id
1 54 1
2 54 2
3 54 3
4 50 1
5 50 2
两个查询在独立运行时都会产生正确的结果:
contact_id
54
但是我试图在其中嵌套一个查询另一个从单个查询中产生相同的结果:
这是我尝试过的:
SELECT c0.contact_id
FROM
(
SELECT contact_id
FROM contacts
WHERE acc_id = 1 AND email LIKE "paul%"
) AS c0
LEFT JOIN contact_tags AS c1
ON c1.contact_id = c0.contact_id
AND (
SELECT c0.contact_id FROM contact_tags c0
INNER JOIN contact_tags c1
on c0.contact_id = c1.contact_id
INNER JOIN contact_tags c2 on c1.contact_id = c2.contact_id
where c0.tag_id = 1 AND c1.tag_id = 2 AND c2.tag_id = 3
)
WHERE c1.id IS NOT NULL
但是我知道这是不对的,因为我只想返回与上述所有条件匹配的单个唯一联系人 ID:
contact_id
54
54
54
54
如果有人可以帮助我对此,我们将不胜感激。
谢谢
I was wondering if anybody here could help with a problem I am having. I am trying to combine 2 mysql queries into one query using sub queries. I currently have the 2 queries separatly which produce the results i want:
Here are the 2 queries:
SELECT contact_id FROM contacts
WHERE acc_id = 1 AND email LIKE "paul%"
SELECT c0.contact_id
FROM contact_tags c0 INNER JOIN contact_tags c1
on c0.contact_id = c1.contact_id INNER JOIN contact_tags c2
on c1.contact_id = c2.contact_id where c0.tag_id = 1
AND c1.tag_id = 2 AND c2.tag_id = 3
Here is some example data from the tables:
Contacts:
contact_id acc_id email
54 1 [email protected]
Tags:
id contact_id tag_id
1 54 1
2 54 2
3 54 3
4 50 1
5 50 2
Both queries when run independently produce this result which is correct:
contact_id
54
However I am trying to nest one query inside the other to produce the same result from a single query:
Here is what I have tried:
SELECT c0.contact_id
FROM
(
SELECT contact_id
FROM contacts
WHERE acc_id = 1 AND email LIKE "paul%"
) AS c0
LEFT JOIN contact_tags AS c1
ON c1.contact_id = c0.contact_id
AND (
SELECT c0.contact_id FROM contact_tags c0
INNER JOIN contact_tags c1
on c0.contact_id = c1.contact_id
INNER JOIN contact_tags c2 on c1.contact_id = c2.contact_id
where c0.tag_id = 1 AND c1.tag_id = 2 AND c2.tag_id = 3
)
WHERE c1.id IS NOT NULL
However I know this is not right as i want to return just the single unique contact id which matches all conditions as above:
contact_id
54
54
54
54
If anyone could help me out with this it would be much appreciated.
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
或者:
Or:
要让查询返回从第一个和第二个查询返回的所有联系人,请使用以下命令:(
假设第一个查询不返回重复的
contact_id
)To have a query that returns all contacts that would be returned from both the first and the second query, use this:
(assuming that the first one does not return duplicate
contact_id
s)