LEFT OUTER JOIN WITH INNER JOINS
Hello -
I am a bit stuck on a LEFT OUTER JOIN. I can get the query to run OK when leaving needed data out of the query (other inner joins). Basically, this is an booking system for a tour company. Some people arrive by cruise ship while others do not. I need this query to gather the customer Booking info from multiple tables and the cruise ship information for those bookings that arrive by ship and return a null ship name for those that dont.
Each ship has a name and a scheduled arrival date. the shipSchedule table holds the data for the scheduled dates and references the ship name from another table(because the same ship comes into town on multiple dates). To eliminate blank entries in the booking table (for those customers not on a ship) there is another table that stores the shipSchedule_ID and the Booking_ID for Bookings that arrive by ship. So, this query needs to get "shipName" from "cruiseShips" then reference the "shipSchedule" table to get the "shipSchedule_ID" then reference the "shipSchedule_ID" in the "ArriveByShip" table to reference "ArriveByShip.Booking_ID" to "Bookings.Booking_ID" field on order to correctly display the ship name the person comes in on for their Booking (possible to have more than one booking)..
The query (this works....but is incomplete)
SELECT
Booking.Booking_ID,
Booking.ticket,
Booking.amountBilled,
Booking.tourDate,
Booking.NumberInParty,
paymentTypes.paymentType,
Users.lastName,
ArriveByShip.ShipSchedule_ID
FROM Booking LEFT OUTER JOIN ArriveByShip ON ArriveByShip.Booking_ID = Booking.Booking_ID,
paymentTypes,
Users
WHERE
paymentTypes.paymentType_ID = Booking.paymentType_ID AND
Users.user_ID = Booking.User_ID AND
Booking.tourDate = '2009-5-21'
This query returns:(sorry if this data gets jumbled on display):
Booking_ID | ticket | amountBilled | tourDate | NumberInParty | paymentType | lastName | ShipSchedule_ID
29 | 5210933 | 710 | 2009-05-21 | 4 | Credit Card | Stockland | NULL |
32 | 12345 | 330 | 2009-05-21 | 3 | Credit Card | Johnson | NULL
33 | 7218692 | 630 | 2009-05-21 | 6 | Account | Temple | 41
34 | NULL | NULL | 2009-05-21 | 1 | Account | Ward | 108
35 | NULL | NULL | 2009-05-21 | 1 | Cash | Ward | NULL
36 | NULL | NULL | 2009-05-21 | 1 | Cash | Ward | 200
38 | 12356 | 220 | 2009-05-21 | 2 | Credit Card | Devine | 40
The problem is this: The inner joins (as you can see) are working fine (ie Credit Card is a reference to paymentType_ID). The LEFT OUTER JOIN is also working because even Booking results with NO shipsSchedule_ID are being returned. The problem arrises when I try to create another join to reference the ships name by the shipSchedule_ID (41, 108, 200, 40 - are shipSchedule_IDs) (SELECT cruiseShips.shipName FROM cruiseShips, shipSchedule WHERE cruiseShip_ID = shipSchedule.cruiseShip_ID). When I attempt to display the ships name rather than its schedule_ID the query DOES NOT return Bookings with no shipSchedule_ID......it only returns Bookings WHERE shipSchedule_ID IS NOT NULL.
The ships name join is dependent on the LEFT OUTER JOIN.....Ive tried nested it...but it didnt work..
Do I need another OUTER JOIN? Do I need to NEST my INNER JOINS?
Any help on how to complete this query would help a ton.....thank you in advance
Adam
Edited 2 time(s). Last edit at 03/28/2009 03:24PM by Adam Stockland.
Subject
Written By
Posted
LEFT OUTER JOIN WITH INNER JOINS
March 28, 2009 02:55PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.