CODEWITHSUNDEEP
mysqlsqljoin
Juniantoじゅにあんと
Juniantoじゅにあんと

Reputation: 65

How to using INNER JOIN if one table is empty or no data

I have two tables; room and reservation.

data on room

id_room | name_room | available_room
1         Class3      6
2         Class2      8
3         Class1      6

data on reservation is empty

id_reservation | id_room | reserved_qty | checkin_date | checkout_date
1
2
3

If the guest wants to book a room, the website will display all the available room during data on table reservation is empty (like table above), but, if there is data on table reservation then the website will show remaining quantity of room (result from reduction between room.available_room reservation.reserved_qty (room.available_room - reservation.reserved_qty)) according to the date of booking and date already booked (checkin_date and checkout_date).

Upvotes: 1

Views: 4075

Answers (2)

200_success
200_success

Reputation: 7582

I don't understand why you would have "defective" rows in the reservation table where the id_room column is NULL. But that doesn't change the proposed solution, since those defective rows would simply be disregarded, since a NULL value for id_room would never successfully join anything.

What you want is an OUTER JOIN instead of an INNER JOIN, so that the lack of a reservation doesn't prevent a room from appearing in the result.

SELECT room.id_room
     , room.name_room
     , room.available_room - SUM(COALESCE(reservation.reserved_qty, 0)) AS remaining_rooms
    FROM room
        LEFT OUTER JOIN reservation
            ON room.id_room = reservation.id_room
    WHERE
        checkin_date IS NULL OR
        @day BETWEEN checkin_date AND checkout_date
    GROUP BY room.id_room, room.name_room;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

The comment by @Thilo is sensible, since you might not really have records in the reservation table which are just sitting there emtpy. But if you are, one way to deal with "empty" data would be to use the COALSECE() function when computing the difference:

SELECT r1.id_room, r1.name_room,
    r1.available_room - COALESCE(r2.reserved_qty, 0) AS numAvailable
FROM room r1
    INNER JOIN reservation r2
    ON r1.id_room = r2.id_room

Upvotes: 0

Related Questions