Help with SQL Query

Question

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.

Answer 1

Every tour_hotel probably has more than one hotelrecord related to it. Thats why you get so many records. If you don't need any information about the hotel you can just select what you need from client and use distinct.

select distinct c.clientid, c.name
from explorer.booking_record b
inner join explorer.client c on b.labelno = c.labelno
inner join explorer.tour_hotel t on b.tourrefcode = t.tourcode
inner join explorer.hotelrecord h on t.hotelcode = h.hotelref
where
 b.bookingdate != '0000-00-00' and
 b.tourdeparturedate != '0000-00-00' and
 h.hotelgroup = "LPL" and 
 year(b.tourdeparturedate) >= (year(now()) - 5)

Answer 2

I couldn't tell what your primary key on the client table was, so I made one up called client_id.

This query will get you a list of the clients that visited 5 or more different LPL hotels and the hotels they visited:

SELECT c.client_id, h.hotelref FROM client c
INNER JOIN booking_record b ON b.labelno = c.labelno
INNER JOIN tour_hotel t ON t.tourcode = b.tourrefcode
INNER JOIN hotelrecord h ON h.hotelref = t.hotelcode
WHERE b.bookingdate > '0000-00-00' AND b.bookingdate IS NOT NULL AND
  b.tourdeparturedate BETWEEN DATE_ADD(NOW(), INTERVAL -5 YEAR) AND NOW() AND
  h.hotelgroup = "LPL"
GROUP BY c.client_id, h.hotelref
HAVING COUNT(c.client_id) > 4

If you want just the list of clients, use DISTINCT and remove the hotel column:

SELECT DISTINCT c.client_id FROM client c
INNER JOIN booking_record b ON b.labelno = c.labelno
INNER JOIN tour_hotel t ON t.tourcode = b.tourrefcode
INNER JOIN hotelrecord h ON h.hotelref = t.hotelcode
WHERE b.bookingdate > '0000-00-00' AND b.bookingdate IS NOT NULL AND
  b.tourdeparturedate BETWEEN DATE_ADD(NOW(), INTERVAL -5 YEAR) AND NOW() AND
  h.hotelgroup = "LPL"
GROUP BY c.client_id, h.hotelref
HAVING COUNT(c.client_id) > 4