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

User · Answer

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

User · Answer

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

User · Answer

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  quot   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

User · Answer

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

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

Examples related to mysql

Examples related to sql

Examples related to join