SQL - Itinerary / Invoice

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

User avatar
whitewater4562000
Posts: 66
Joined: Sat Aug 23, 2008 4:22 pm UTC
Location: Scotland
Contact:

SQL - Itinerary / Invoice

Postby whitewater4562000 » Fri Jun 05, 2015 3:02 pm UTC

Hi,

I'm confused and also suffering from Friday blues...

Basically I'm looking to pull all the information from my itinerary tables in my database and create an invoice from it.
(Invoice bit fine)...

Individually the queries are all fine but pulling them together ain't happening and also is there a better way of doing it.

Code: Select all

SELECT
  accommodationDate, accommodationName, duration, roomType, boardBasis,
  accommodation.notes, flightDate, a1.airportName, a2.airportName, flightNum,
  departureTime, arrivalTime, cruiseDate, departurePoint, arrivalPoint,
  cruiseCompany, cruiseName, cabinType, misc.notes
FROM itinerary
LEFT JOIN accommodation ON accommodation.itineraryID = itinerary.itineraryID
LEFT OUTER JOIN flights ON flights.itineraryID = itinerary.itineraryID
LEFT OUTER JOIN cruises ON cruises.itineraryID = itinerary.itineraryID
LEFT OUTER JOIN misc ON misc.itineraryID = itinerary.itineraryID
LEFT JOIN bookings ON itinerary.bookingID = bookings.bookingID
LEFT OUTER JOIN airports a1 ON flights.flightFrom = a1.airportID
LEFT OUTER JOIN airports a2 ON flights.flightTo = a2.airportID
WHERE bookings.bookingRef = "000001"


Currently only pulling in the flight info and subsequently the airport names.
Assume the problem is with the joins, tried inner/outer/left/right etc with no success.
Have read upon it but not really helping.

Greatly appreciate any help.

BedderDanu
Posts: 39
Joined: Tue Jan 14, 2014 6:18 am UTC

Re: SQL - Itinerary / Invoice

Postby BedderDanu » Mon Jun 08, 2015 11:25 pm UTC

If I had to hazard a guess, I think there is some strange interactions going on with the bookings join and the WHERE clause.

Try either of the following:

Code: Select all

SELECT
  accommodationDate
 ,accommodationName
 ,duration
 ,roomType
 ,boardBasis
 ,accommodation.notes
 ,flightDate
 ,a1.airportName
 ,a2.airportName
 ,flightNum
 ,departureTime
 ,arrivalTime
 ,cruiseDate
 ,departurePoint
 ,arrivalPoint
 ,cruiseCompany
 ,cruiseName
 ,cabinType
 ,misc.notes
FROM
  itinerary
  JOIN accommodation ON accommodation.itineraryID = itinerary.itineraryID
  LEFT JOIN flights ON flights.itineraryID = itinerary.itineraryID
  LEFT JOIN cruises ON cruises.itineraryID = itinerary.itineraryID
  LEFT JOIN misc ON misc.itineraryID = itinerary.itineraryID
  LEFT JOIN bookings ON itinerary.bookingID = bookings.bookingID AND bookings.bookingRef = "000001"
  LEFT JOIN airports AS a1 ON flights.flightFrom = a1.airportID
  LEFT JOIN airports AS a2 ON flights.flightTo = a2.airportID


or

Code: Select all

SELECT
  accommodationDate
 ,accommodationName
 ,duration
 ,roomType
 ,boardBasis
 ,accommodation.notes
 ,flightDate
 ,a1.airportName
 ,a2.airportName
 ,flightNum
 ,departureTime
 ,arrivalTime
 ,cruiseDate
 ,departurePoint
 ,arrivalPoint
 ,cruiseCompany
 ,cruiseName
 ,cabinType
 ,misc.notes
FROM
  bookings
  JOIN itinerary ON itinerary.bookingID = bookings.bookingID
  LEFT JOIN accommodation ON accommodation.itineraryID = itinerary.itineraryID
  LEFT JOIN flights ON flights.itineraryID = itinerary.itineraryID
  LEFT JOIN cruises ON cruises.itineraryID = itinerary.itineraryID
  LEFT JOIN misc ON misc.itineraryID = itinerary.itineraryID
  LEFT JOIN airports AS a1 ON flights.flightFrom = a1.airportID
  LEFT JOIN airports AS a2 ON flights.flightTo = a2.airportID
WHERE
  bookings.bookingRef = "000001"


Do either of these work for what you need?

User avatar
whitewater4562000
Posts: 66
Joined: Sat Aug 23, 2008 4:22 pm UTC
Location: Scotland
Contact:

Re: SQL - Itinerary / Invoice

Postby whitewater4562000 » Wed Jun 10, 2015 2:38 pm UTC

Feel like such a d**k, was missing the itinerary id value in the accommodation table. So all working as expected :D

I am concerned with the number of joins, Is there a better way of doing it?

User avatar
Xanthir
My HERO!!!
Posts: 5410
Joined: Tue Feb 20, 2007 12:49 am UTC
Location: The Googleplex
Contact:

Re: SQL - Itinerary / Invoice

Postby Xanthir » Wed Jun 10, 2015 4:26 pm UTC

Such is the nature of highly denormalized data. Assuming all the tables are producing data for the query (or are linking tables between tables that produce data), you can't avoid those joins.

You can, of course, minimize the damage by using indexes appropriately. I assume you've indexed all the tables?
(defun fibs (n &optional (a 1) (b 1)) (take n (unfold '+ a b)))

User avatar
poxic
Eloquently Prismatic
Posts: 4751
Joined: Sat Jun 07, 2008 3:28 am UTC
Location: Left coast of Canada

Re: SQL - Itinerary / Invoice

Postby poxic » Wed Jun 10, 2015 10:38 pm UTC

Is everything 1:1, or do you get lots of duplicated rows? Hard to tell from here.
A man who is 'ill-adjusted' to the world is always on the verge of finding himself. One who is adjusted to the world never finds himself, but gets to be a cabinet minister.
- Hermann Hesse, novelist, poet, Nobel laureate (2 Jul 1877-1962)


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 5 guests