CODEWITHSUNDEEP
mysqlsqldatabaseselect
cbarlow123
cbarlow123

Reputation: 217

MySQL- Select Where second select result is gathered

I have this query. The second part is getting the correct values but the final output data still includes the row where the column is equal to the value from the select.

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

I'm trying to get it to work saying SELECT all from roomfacilities WHERE room 'does not equal' result from select.

Thanks for any help.

Upvotes: 3

Views: 268

Answers (2)

John Woo
John Woo

Reputation: 263723

by using LEFT JOIN you can get all records from table roomfacilities when the value of room_assigned from table allocation is 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

Upvotes: 3

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Use the NOT IN predicate:

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;

Upvotes: 4

Related Questions