MySQL Forums
Forum List  »  Newbie

LEFT OUTER JOIN WITH INNER JOINS
Posted by: Adam Stockland
Date: March 28, 2009 02:55PM

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.

Options: ReplyQuote


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.