MySQL 加入多个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8018550/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Join with multiple conditions
提问by Mihai Stancioiu
I have a problem with an sql query, actually a simple query but I can't figure up what am I missing, so I came to ask your help... So, what i have to do
我的 sql 查询有问题，实际上是一个简单的查询，但我无法弄清楚我错过了什么，所以我来寻求您的帮助...所以，我必须做什么
I have two tables roomsand rooms facilities...and I have to select the rooms with desired facilities..
我有两个表rooms和rooms facilities......我不得不选择与所希望的设施客房..
If I select a room with one facility (facility with id=4 - id_fu - )...using the following query
如果我选择一个房间有一个设施（设施 id=4 - id_fu - ）...使用以下查询
SELECT u.* FROM rooms u
JOIN facilities_r fu
ON fu.id_uc = u.id_uc
AND fu.id_fu = '4'
WHERE 1
AND vizibility='1'
GROUP BY id_uc
ORDER BY u_premium desc, id_uc DESC
everything it's ok.
一切安好。
But if I want to select the room with more facilities, let's say facilities with id=4, and id=3 ..using the following query
但是，如果我想选择设施较多的房间，假设设施的 id=4 和 id=3 ..使用以下查询
SELECT u.* FROM room u
JOIN facilities_r fu
ON fu.id_uc=u.id_uc
AND fu.id_fu = '4'
AND fu.id_fu = '3'
WHERE 1
AND vizibility = '1'
GROUP BY id_uc
ORDER BY u_premium DESC, id_uc DESC
it doesn't work.
它不起作用。
I can't understand why it doesn't work, but I can't figure up how to put the condition...
我不明白为什么它不起作用，但我无法弄清楚如何放置条件......
Thanks, Mihai
谢谢，米海
回答by Andy Fleming
You can group conditions with parentheses. When you are checking if a field is equal to another, you want to use OR. For example WHERE a='1' AND (b='123' OR b='234').
您可以使用括号对条件进行分组。当您检查一个字段是否等于另一个字段时，您想使用OR. 例如WHERE a='1' AND (b='123' OR b='234')。
SELECT u.*
FROM rooms AS u
JOIN facilities_r AS fu
ON fu.id_uc = u.id_uc AND (fu.id_fu='4' OR fu.id_fu='3')
WHERE vizibility='1'
GROUP BY id_uc
ORDER BY u_premium desc, id_uc desc
回答by Ananth
SELECT
u . *
FROM
room u
JOIN
facilities_r fu ON fu.id_uc = u.id_uc
AND (fu.id_fu = '4' OR fu.id_fu = '3')
WHERE
1 and vizibility = '1'
GROUP BY id_uc
ORDER BY u_premium desc , id_uc desc
You must use OR here, not AND.
此处必须使用 OR，而不是 AND。
Since id_fu cannot be equal to 4 and 3, both at once.
由于 id_fu 不能同时等于 4 和 3。
回答by Andy Hobbs
If you join the facilities table twice you will get what you are after:
如果你两次加入设施表，你会得到你想要的：
select u.*
from room u
JOIN facilities_r fu1 on fu1.id_uc = u.id_uc and fu1.id_fu = '4'
JOIN facilities_r fu2 on fu2.id_uc = u.id_uc and fu2.id_fu = '3'
where 1 and vizibility='1'
group by id_uc
order by u_premium desc, id_uc desc
