Having issues with a MySQL Join that needs to meet multiple conditions

Question

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

If I select a room with one facility (facility with id=4 - id_fu - ). using the following query Everything it's ok:

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  

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 it doesn't work:

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 don't understand why it doesn't work, but I can't figure up how to put the condition.

Answer 1

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 

Answer 2

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.

Since id_fu cannot be equal to 4 and 3, both at once.