CODEWITHSUNDEEP
oracle-database
wholee1
wholee1

Reputation: 1501

Oracle SQL developer how to display NULL value of the foreign key

I am going to try to use left outer join between Ticket and Membership. However, it does not display the foreign key of NULL values on Ticket. Could you give me some answer for this what's wrong with this query? Thanks.

FROM Ticket t, Production pro, Performance per, Price, Price_level, Booking, Customer, Customer_Concession ccons, Membership, Member_concession mcons
WHERE t.performanceid = per.performanceid AND
  t.PRODUCTIONID = Price.PRODUCTIONID AND
  t.levelId = Price.levelId AND
  Price.PRODUCTIONID = pro.PRODUCTIONID AND
  Price.levelId = Price_level.levelId AND
  Booking.bookingId (+) = t.bookingId AND
  Customer.customerId = Booking.customerId AND
  ccons.cConcessionId (+) = Customer.cConcessionId AND  
  Membership.membershipId (+) = t.membershipId AND
  Membership.mConcessionId = mcons.mConcessionId 
ORDER BY t.ticketId

Upvotes: 0

Views: 599

Answers (2)

Michael Broughton
Michael Broughton

Reputation: 4055

To expand on Dave's observation, and to give you an example of SQL92 syntax, please please please learn it and get away from Oracle's own outer join syntax. 

FROM
TICKET t
JOIN Performance per
ON per.performance_id = t.performance_id
JOIN Production pro
ON pro.produciton_id = t.production_id
JOIN PRICE pr
ON pr.production_id = pro.production_id
AND pr.levelId = t.level_id
JOIN price_level pl
ON   pl.levelid = pr.levelid
LEFT OUTER JOIN booking b
on  b.booking_id = t.booking_id
LEFT OUTER JOIN customer cus
on  cus.customer_id = b.customer_id
LEFT OUTER JOIN customer_concession cons
ON cons.concession_id = cus.concession_id
LEFT OUTER JOIN memebership m
ON M.membership_id = t.membership_id
LEFT OUTER JOIN membership_concession mc
ON mc.mConcession_id = m.mConcession_id
Order by t.ticketid

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48121

One potential problem you have is these two conditions:

  Booking.bookingId (+) = t.bookingId AND
  Customer.customerId = Booking.customerId AND

Since you're doing an outer join to Booking, its columns will appear as NULL when no match is found; but then your doing a normal join to Customer, so those rows will be eliminated since NULL cannot be equal to anything. You may want to change the second line to an outer join as well.

But, I don't know if that's your primary problem, since I don't actually understand exactly what you're asking. What do you mean by "NULL value of the foreign key"? You haven't specified what your foreign keys are.

Upvotes: 4

Related Questions