CODEWITHSUNDEEP
sqlsql-servert-sql
m.jakobsen
m.jakobsen

Reputation: 13

Joining tables and finding values that do not exist

I am having some issues with joining tables to get null values, and I can't find what I am doing wrong.

The case: I am trying to make a cinema system, where I have made entities that match the cinema.

I have a Hall, Row and Seat table, and a Show table that holds the value for movies and what hall it will be played in. To bond everything together, I have made a Reservation table that is keeping track of what seats to that specific show is taken.

My entities look like this:

Entities image

My problem: I am trying to fetch all free seats for the show, I can get all seats for the show, but when I try to add the Reservation to get the free ones I get no records.

My query that is able to fetch all seats:

    SELECT show.id            AS ShowID, 
       seat.id            AS SeatID, 
       seat.rowid         AS RowID, 
       show.hallid        AS HallId, 
       reservation.seatid AS Expr1 
FROM   show 
       INNER JOIN hall 
               ON show.hallid = hall.id 
       FULL OUTER JOIN seat 
                    ON hall.id = seat.hallid 
       LEFT OUTER JOIN reservation 
                    ON reservation.showid = show.id 
WHERE  ( show.id = 1 ) 
       AND ( reservation.seatid IS NULL ) 
ORDER  BY reservation.showid, 
          rowid

Upvotes: 0

Views: 405

Answers (2)

forpas
forpas

Reputation: 164064

You need INNER joins between Show, Hall, Row and Seat and a LEFT join to Reservation, so you can filter out the matched rows:

SELECT s.Id AS ShowID, t.Id AS SeatID, t.RowId AS RowID, s.HallId
FROM Show s
INNER JOIN Hall h ON h.Id = s.HallId
INNER JOIN Seat t ON t.HallId = h.Id
INNER JOIN Row w ON w.HallId = h.Id AND w.Id = t.RowId 
LEFT JOIN Reservation r ON r.ShowId = s.Id AND r.HallId = h.Id AND r.SeatId = t.Id AND r.RowId = w.Id
WHERE (s.Id = 1) AND (r.SeatId IS NULL)

Upvotes: 1

Rik Maton
Rik Maton

Reputation: 135

Replace:

INNER JOIN Hall ON Show.Id = Hall.Id FULL OUTER JOIN

With:

INNER JOIN Hall ON Show.HallId = Hall.Id FULL OUTER JOIN

While it might not be the full answer to your question, i think this might cause issues for you too.

Upvotes: 1

Related Questions