Clever Geek Handbook

MySQL - select where the second selection result is collected

I have this request. The second part gets the correct values, but the final output still contains the row where the column is equal to the value from the selected one.

SELECT * 
FROM roomfacilities 
WHERE room <> '(SELECT room_assigned 
                FROM allocation 
                WHERE booking_id = 01010106)';

I am trying to get it to work by saying that SELECT is all from roomfacilities

WHERE the room "not equal" comes from the selection.

Thanks for any help.

+3


source to share


2 answers


Use a predicate NOT IN

:

SELECT *
FROM roomfacilities 
WHERE room NOT IN(SELECT room_assigned 
                  FROM allocation 
                  WHERE booking_id = 01010106);


Or LEFT JOIN

::

SELECT f.*
FROM roomfacilities f
LEFT JOIN allocation a  ON f.room       = a.room_assigned 
                       AND a.booking_id = 01010106
WHERE a.room_assigned IS NULL;
+4


source


using LEFT JOIN

you can get all records from a table roomfacilities

when the value room_assigned

from the table allocation

is equal NULL

.



SELECT  a.* 
FROM    roomfacilities a
        LEFT JOIN allocation b
            ON a.room = b.room_assigned AND
               b.booking_id = 01010106
WHERE   b.room_assigned IS NULL
+3


source






More articles:


All Articles