0

The database has several tables:

  • Tours
  • Bookings
  • Guides

Now every tour can have n bookings and every guide can have n tours. However, on my homepage I only want to show a list of 5 tours with a limit of 1 tour per guide. However, if I take the bookings from a left join, I need to group by "tourId", but on the other side I also need to group by "tourGuideId" to have a limit of 1. If I GROUP BY tourGuideId it shows me only one tour per guide, but makes problems with the LEFT JOIN and shows bookings although there aren't. If I GROUP BY tourId it works with the LEFT JOIN but not with the limit of 1 per guide.

Here is the simple query:

SELECT f.*, SUM(b.bookingSeats) AS bookings
FROM tours AS f 
LEFT JOIN bookings AS b ON f.tourId = b.bookingTourId 
WHERE f.tourActive = '1'
GROUP BY f.tourGuideId
ORDER BY f.tourId DESC 
LIMIT 5

How to proceed?

Edit 1: a simple "GROUP BY f.tourGuideId, f.tourId" doesn't work. It makes the LEFT JOIN work but doesn't limit to 1 result per guide.

Edit 2: Sample result should be:

tourId, tourGuideId, tourTitle, tourDate, tourActive, bookings
5, 1, 'Hello World', '2016-12-01 08:00:00', 1, NULL
9, 3, 'Tour Paris', '2017-03-01 08:00:00', 1, 3

Bookings Table structure:

bookingId, bookingStatus, bookingUserId, bookingDate, bookingPaid

Tour Table structure:

tourId, tourGuideId, tourTitle, tourDate, tourActive
Dharman
  • 30,962
  • 25
  • 85
  • 135
Lars
  • 85
  • 9
  • Can you also post some sample input data along with expected result set? – Giorgos Betsos Dec 08 '16 at 07:28
  • *Sample result should be* ... based on which input? – Giorgos Betsos Dec 08 '16 at 07:41
  • @GiorgosBetsos the sample result is that what I'd like to receive. Instead, with GROUP BY tourId, I get tours by several guides and with GROUP BY tourGuideId I get only one tour per guide but left join is not working. – Lars Dec 08 '16 at 07:43
  • Is `bookings` supposed to be the total bookings for all tours by that guide, or just the number of bookings for the 1 tour that you show for each guide? – Barmar Dec 08 '16 at 07:53
  • If the guide can have n tours, which of those tours should be shown? Just pick a random one? – Barmar Dec 08 '16 at 07:54
  • @Barmar Sorry, it should show the SUM of the seats of all the bookings on the tour and the lastest happening tour should be shown (so order by tourDate or tourId DESC) – Lars Dec 08 '16 at 08:11

1 Answers1

1

Start with a query that gets the latest tour for each guide, as shown in SQL Select only rows with Max Value on a Column.

Then join that with a subquery that gets the total bookings for each guide.

SELECT f.*, bookings
FROM tours AS f
JOIN (SELECT MAX(tourId) AS maxTourId
      FROM tours
      GROUP BY tourGuideId) AS f1
ON f.tourId = f1.maxTourId
JOIN (
    SELECT tourGuideId, SUM(b.bookingSeats) AS bookings
    FROM tours AS f
    LEFT JOIN bookings AS b ON f.tourId = b.bookingsTourId
    GROUP BY tourGuideId) AS b
ON f.tourGuideId = b.tourGGuideId
ORDER BY tourId DESC
LIMIT 1
Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612