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

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.

Solution 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

Solution 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.