CODEWITHSUNDEEP
sqlsql-server
KIRPAL SINGH
KIRPAL SINGH

Reputation: 305

How to do full outer join. Not getting expected results

I want to display all the seat labels with their respective rowid and columnid and additional ticket code from ticket_items to show if tickets are bought or else null. From my understanding, outer join should do my job, but its not.

The problem is as below, 1) In TICKET_ITEMS I only have 2 tickets bought with SEATNO "B1"(ROWID:1 COLID:1 )and "C2"(ROWID:2 COLID:2).

2) It is showing all the seats but with wrong ticket code. if none it should show null (full outer join)

THEATHER

THEATHER

SHOWTIMES

THEATHER

TICKET_ITEMS

THEATHER

SEAT

SEAT

This is my query

SELECT 
  SHOWTIMES.ID AS SHOWTIMESID,
  SHOWTIMES.THEATREID,
  THEATRES.THEATRENAME,
  THEATRES.NUMOFROWS,
  THEATRES.NUMOFCOLS,
  SEAT.SEATNO AS SEATLABEL,
  SEAT.ROWID,
  SEAT.COLUMNID,
  TICKET_ITEMS.SEATNO,
  TICKET_ITEMS.TICKETCODE
FROM
  SEAT  RIGHT OUTER JOIN THEATRES ON SEAT.ROOMID = THEATRES.ID
  RIGHT OUTER JOIN SHOWTIMES ON SHOWTIMES.THEATREID = THEATRES.ID
  RIGHT OUTER JOIN TICKET_ITEMS ON TICKET_ITEMS.SHOWTIMESID = SHOWTIMES.ID
WHERE
  SHOWTIMES.ID = 1
;

My query results

THEATHER

Upvotes: 0

Views: 86

Answers (1)

Avi
Avi

Reputation: 1845

Instead of Right outer join, I think you may need left outer join. Instead of including SHOWTIMES.ID = 1 in where condition, add in and condition while doing join. This will make sure that you are not losing any records from Seats table, and get null or not null values as long as it is matching based on the joins. Also, add another condition in the last join with seats so that you get the records only when seat is valid. 

SELECT 
SHOWTIMES.ID AS SHOWTIMESID,
SHOWTIMES.THEATREID,
THEATRES.THEATRENAME,
THEATRES.NUMOFROWS,
THEATRES.NUMOFCOLS,
SEAT.SEATNO AS SEATLABEL,
SEAT.ROWID,
SEAT.COLUMNID,
TICKET_ITEMS.SEATNO,
TICKET_ITEMS.TICKETCODE
FROM
SEAT  left  OUTER JOIN THEATRES ON SEAT.ROOMID = THEATRES.ID
left OUTER JOIN SHOWTIMES ON SHOWTIMES.THEATREID = THEATRES.ID and SHOWTIMES.ID = 1
left OUTER JOIN TICKET_ITEMS ON TICKET_ITEMS.SHOWTIMESID = SHOWTIMES.ID and  TICKET_ITEMS.SEATNO = SEAT.SEATNO;

Upvotes: 2

Related Questions