MySQL联接有多个条件 [英] MySQL Join with multiple conditions

问题描述

我在使用sql查询时遇到了问题，实际上是一个简单的查询，但是我无法弄清缺少的内容，所以我来寻求您的帮助... 所以，我要做的

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

我有两个桌子rooms和rooms facilities ...，我必须选择具有所需设施的房间.

I have two tables rooms and rooms facilities...and I have to select the rooms with desired facilities..

如果我使用以下查询选择具有一个设施(id = 4-id_fu-的设施)的房间...

If I select a room with one facility (facility with id=4 - id_fu - )...using the following query

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 

一切正常.

但是，如果我要选择具有更多设施的房间，那么可以使用以下查询，例如id = 4和id = 3的设施.

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

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 

它不起作用.

我不明白为什么它不起作用，但我不知道如何处理该情况...

I can't understand why it doesn't work, but I can't figure up how to put the condition...

谢谢，米海

推荐答案

您可以用括号将条件分组.当您检查一个字段是否等于另一个字段时，您想使用OR.例如WHERE a='1' AND (b='123' OR b='234').

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').

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

这篇关于MySQL联接有多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！