CODEWITHSUNDEEP
sqloracle-databaseoracle11g
raven39
raven39

Reputation: 135

Using JOIN with subquery

Hotel( hotelNo, hotelName, hotelAddress, country)
Room (roomNo, hotelNo, type, price)
Guest( guestNo, guestName, guestAddress, country)
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

The question I'm trying to solve is,

--Use 2019-02-05 as today's date. List the details of all rooms at the hotels with 'Grosvener' in name, including the name of the guest staying in the room, if the room is occupied.List in hotelNo, roomNo order.

It runs, but it says 'no rows selected.' what am I possibly missing?

SELECT r.roomNo, r.hotelNo, r.type, r.price, guestName
FROM Room r 
LEFT OUTER JOIN Booking b
ON r.hotelNo = b.hotelNo
NATURAL JOIN Guest 
WHERE r.hotelNo IN 
    (SELECT hotelNo FROM Hotel
     WHERE hotelName LIKE 'Grosvener%')
AND ((b.dateFrom <= DATE'2019-02-05') AND 
(dateTo IS NULL OR dateTo >= DATE'2019-02-05'))
ORDER BY hotelNo, roomNo;

Upvotes: 0

Views: 90

Answers (4)

raven39
raven39

Reputation: 135

Thank you all for answer my question. 

SELECT r.roomNo, r.hotelNo, r.type, r.price, guestName
FROM Hotel h
    JOIN Room r 
        ON r.hotelNo = h.hotelNo
    LEFT JOIN
       (
        SELECT b.hotelNo, b.roomNo, g.guestName, b.dateTo, b.dateFrom
        FROM Guest g
            JOIN Booking b
            ON g.guestNo = b.guestNo
        WHERE b.dateFrom <= DATE'2019-02-05'
        AND (dateTo IS NULL OR dateTo >= DATE'2019-02-05')
        ) bo
    ON r.hotelNo = bo.hotelNo
    AND r.roomNo = bo.roomNo
WHERE h.hotelName LIKE '%Grosvenor%'
ORDER BY h.hotelNo, r.roomNo
;

how do you think about this code tho? This seems like the only code that gives me right answer but It's way too long.

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

I suppose there is problem related to the parantheses usage for the subquery which starts with SELECT hotelNo ..., the following usage might help :

SELECT r.roomNo, r.hotelNo, r.type, r.price, guestName
  FROM Room r
  LEFT OUTER JOIN Booking b
    ON r.hotelNo = b.hotelNo NATURAL
  JOIN Guest
 WHERE r.hotelNo IN
  (SELECT hotelNo
     FROM Hotel
    WHERE hotelName LIKE 'Grosvener%' 
      AND ( ( b.dateFrom <= date'2019-02-05' AND dateTo >= date'2019-02-05' )
         OR ( dateTo IS NULL )))
 ORDER BY hotelNo, roomNo;

Upvotes: 1

Virgil Ionescu
Virgil Ionescu

Reputation: 337

Why do you use natural join? Use left outer join (in case there are no guests for a specified booking) .

SELECT r.roomNo, r.hotelNo, r.type, r.price, guestName
FROM Room r 
LEFT OUTER JOIN Booking b
ON r.hotelNo = b.hotelNo
LEFT OUTER JOIN Guest g
ON b.guestNo=g.guestNo
WHERE r.hotelNo IN 
(SELECT hotelNo FROM Hotel
WHERE hotelName LIKE 'Grosvener%')
AND ((b.dateFrom <= DATE'2019-02-05') AND 
(dateTo IS NULL OR dateTo >= DATE'2019-02-05'))
ORDER BY hotelNo, roomNo;

Upvotes: 1

Aravindi Amarasinghe
Aravindi Amarasinghe

Reputation: 212

I think you have to use an identifier to the Guest table as well. Please try following query. 

SELECT r.roomNo, r.hotelNo, r.type, r.price, g.guestName
FROM Room r 
LEFT OUTER JOIN Booking b
ON r.hotelNo = b.hotelNo
NATURAL JOIN Guest g
WHERE r.hotelNo IN 
(SELECT hotelNo FROM Hotel
WHERE hotelName LIKE 'Grosvener%')
AND ((b.dateFrom <= DATE'2019-02-05') AND 
(dateTo IS NULL OR dateTo >= DATE'2019-02-05'))
ORDER BY r.hotelNo, r.roomNo;

Upvotes: 1

Related Questions