Help with SQL Query

Posted by djfrear on Stack Overflow See other posts from Stack Overflow or by djfrear
Published on 2010-03-30T13:32:08Z Indexed on 2010/03/30 13:33 UTC
Read the original article Hit count: 306

Filed under:
|

With regards to the following statement:

Select
*
From
       explorer.booking_record booking_record_
Inner Join explorer.client client_ On booking_record_.labelno = client_.labelno
Inner Join explorer.tour_hotel tour_hotel_ On tour_hotel_.tourcode = booking_record_.tourrefcode
Inner Join explorer.hotelrecord hotelrecord_ On tour_hotel_.hotelcode = hotelrecord_.hotelref

Where booking_record_.bookingdate Not Like '0000-00-00' And
  booking_record_.tourdeparturedate Not Like '0000-00-00' And
  hotelrecord_.hotelgroup = "LPL" And Year(booking_record_.tourdeparturedate)
  Between Year(AddDate(Now(), Interval -5 Year)) And Year(Now())

My MySQL skills are certainly not up to scratch, the actual result set I wish to find is "a customer who has been to 5 or more LPL hotels in the past 5 years". So far I havent got as far as dealing with the count as I'm getting a huge number of results with some 250+ per customer.

I assume this is to do with the way I'm joining tables. Schema wise the booking_record table contains a tour reference code, which links to tour_hotel which then contains a hotelcode which links to hotelrecord. This hotelrecord table contains the hotelgroup.

The client table is joined to the booking_record via a booking reference and a client may have many bookings.

If anyone could suggest a way for me to do this I'd be very grateful and hopefully learn enough to do it myself next time! I've been scratching my head over this one for a few hours now!

Customers may have many bookings within booking_record

Daniel.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query