Having issues with a MySQL Join that needs to meet multiple conditions
MysqlSqlJoinMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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 - Mysql
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.
Solution 3 - Mysql
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
Solution 4 - Mysql
also this should work (not tested):
SELECT u.*
FROM room u
JOIN facilities_r fu ON fu.id_uc = u.id_uc AND u.id_fu IN(4,3)
WHERE 1 AND vizibility = 1
GROUP BY id_uc
ORDER BY u_premium desc , id_uc desc
If u.id_fu
is a numeric field then you can remove the '
around them.
The same for vizibility
. Only if the field is a text field (data type char, varchar or one of the text-datatype e.g. longtext) then the value has to be enclosed by '
or even "
.
Also I and Oracle too recommend to enclose table and field names in backticks. So you won't get into trouble if a field name contains a keyword.