Help with SQL Query
- by djfrear
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.
