CODEWITHSUNDEEP
mysqlsqlin-clause
JohnK
JohnK

Reputation: 119

SQL IN Clause - get back IN elements that did not match

I'd like to run an SQL query to determine the occupants of a number of rooms. I'd also like to see which rooms are empty.

I can find the occupants using SQL of the form:

SELECT Room, OccupantName
FROM Rooms
WHERE Rooms IN ("Room 1", "Room 2", "Room 3", "Room 4")

However, if only Rooms 1 and 2 have occupants, eg

Room    OccupantName
Room 1, Person A
Room 2, Person B

how can I get something of the form:

Room    OccupantName
Room 1, Person A
Room 2, Person B
Room 3, Nobody
Room 4, Nobody

Is there a way to select out the elements of the IN clause that did not return results and show "Nobody"?

Upvotes: 3

Views: 1965

Answers (5)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

Since MySQL 8.0 where introduced JSON_TABLE you can use next approach:

  • generate table from JSON rooms list and use LEFT JOIN with your table
SELECT 
    all_rooms.room Room, 
    COALESCE(rooms.name, 'NoBody') OccupiedBy
FROM JSON_TABLE(
    '["Room 1","Room 2","Room 3","Room 4"]',
    '$[*]' COLUMNS (room VARCHAR(64) PATH '$[0]')
) all_rooms
LEFT JOIN rooms USING(room);

SQL editor online

Result:

+========+============+
| Room   | OccupiedBy |
+========+============+
| Room 1 | Person A   |
+--------+------------+
| Room 2 | NoBody     |
+--------+------------+
| Room 3 | NoBody     |
+--------+------------+
| Room 4 | Person B   |
+--------+------------+

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

Just a single COALESCE or ISNULL will help you to get the desired output. You are almost near to your solution and there are many ways as you can see in the given answers but I suggest you to do slight change your code as below: 

SELECT Room, COALESCE(OccupantName, 'Nobody') AS OccupantName
FROM Rooms
WHERE Rooms IN ('Room 1', 'Room 2', 'Room 3', 'Room 4')

For MYSQL you can use IFNULL in the same manner

Upvotes: 0

Adham
Adham

Reputation: 9

You should have two separate tables. one with list of rooms and second on the existing tables then you can select by LEFT join experession.

for example the RoomsList table will be like:

Room

Room 1

Room 2

Room 3

Room 4

Room 5

Room 6

Room 7

and then the query will be like:

SELECT Room, OccupantName

FROM RoomsList Left Join Rooms

ON RoomsList.Room=Rooms.Room

WHERE RoomsList.Room IN ("Room 1", "Room 2", "Room 3", "Room 4")

Upvotes: 0

Owain Esau
Owain Esau

Reputation: 1922

Assuming the rooms without an occupant are set to NULL, you could use a case statement:

SELECT Room, CASE WHEN OccupantName IS NULL THEN 'Nobody' Else OccupantName END
FROM   Rooms
WHERE  Room IN ("Room 1", "Room 2", "Room 3", "Room 4")

Or just filter them out with a where clause:

SELECT Room, OccupantName
FROM   Rooms
WHERE  Room IN ("Room 1", "Room 2", "Room 3", "Room 4")
    AND OccupantName IS NOT NULL

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

It appears that your Rooms table only contains data for active occupancies. This makes sense in many ways, because you don't want to store information about people which aren't really occupying a room. But it poses a challenge to generate your desired result set, because the missing rooms aren't present in Rooms.

One option here is the "calendar table" approach. You can LEFT JOIN a table containing all rooms to your current Rooms table, and then label missing occupants as nobody.

SELECT t1.Room,
       COALESCE(t2.OccupantName, 'Nobody') AS OccupantName
FROM
(
    SELECT "Room 1" AS Room
    UNION ALL
    SELECT "Room 2"
    UNION ALL
    SELECT "Room 3"
    UNION ALL
    SELECT "Room 4"
) AS t1
LEFT JOIN Rooms AS t2
    ON t1.Room = t2.Rooms

Note that I used an line subquery to create a table for all rooms. In practice, you could create an actual table in Workbench containing this information.

Demo here:

SQLFiddle

Upvotes: 4

Related Questions