1

I'm trying to make a SQL query to return values potentially from 3 tables. This will combine all booking information for a booking which can span over multiple days with multiple guests and a single member.

I have a bookings, guests and members table:

Bookings: id, day, booking_id, guest_id, member_id, start_date, end_date

Guests: id, first_name, last_name

Members: id, first_name, last_name

The Bookings table contains a new booking row per day booked per person. Each of these bookings can relate to another by booking_id (These are group bookings). A booking must have a single member related to it but can have multiple guests. However a single booking does not have a column showing it is a group booking other than if the booking_id is else where in the Bookings table.

From what I understand is I want to self-join multiple times (can potentially have ~30 of the same booking_id's) on booking_id

Then left-join on the Members where Bookings.member_id = Members.id WHERE Bookings.member_id != 0

And left-join on the Guests where Bookings.guest_id = Guests.id WHERE Bookings.guest_id != 0

Sorry if this is confusing and thank you for reading it

For example:

Bookings:
+----+-----+------------+-----------+----------+------------+----------+
| id | day | booking_id | member_id | guest_id | start_date | end_date |
+----+-----+------------+-----------+----------+------------+----------+
| 1  | 01  | 476        | 91        | 0        | 01-06-15   | 01-06-15 |
| 2  | 01  | 476        | 0         | 64       | 01-06-15   | 01-06-15 |
| 3  | 01  | 519        | 32        | 0        | 01-06-15   | 01-06-15 |
| 4  | 02  | 634        | 84        | 0        | 02-06-15   | 04-06-15 |
| 5  | 02  | 634        | 0         | 29       | 02-06-15   | 04-06-15 |
| 6  | 02  | 634        | 0         | 15       | 02-06-15   | 04-06-15 |
| 7  | 03  | 634        | 84        | 0        | 02-06-15   | 04-06-15 |
| 8  | 03  | 634        | 0         | 29       | 02-06-15   | 04-06-15 |
| 9  | 03  | 634        | 0         | 15       | 02-06-15   | 04-06-15 |
| 10 | 04  | 634        | 84        | 0        | 02-06-15   | 04-06-15 |
| 11 | 04  | 634        | 0         | 29       | 02-06-15   | 04-06-15 |
| 12 | 04  | 634        | 0         | 15       | 02-06-15   | 04-06-15 |
+----+-----+------------+-----------+----------+------------+----------+

Members:
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 32 | Travis     | Pilsner   |
| 84 | George     | Warrok    |
| 91 | Rob        | Williams  |
+----+------------+-----------+

Guests:
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 15 | Sarah      | Phillips  |
| 29 | Katie      | Hills     |
| 64 | Laura      | Time      |
+----+------------+-----------+

To Produce:

+-----+------------+-----------+----------+------------+----------+--------------------------------------------+
| day | booking_id | member_id | guest_id | start_date | end_date | names                                      |
+-----+------------+-----------+----------+------------+----------+--------------------------------------------+
| 01  | 476        | 91        | 64       | 01-06-15   | 01-06-15 | Rob Williams, Laura Time                   |
| 01  | 519        | 32        | 0        | 01-06-15   | 01-06-15 | Travis Pilsner                             |
| 02  | 634        | 84        | 29, 15   | 02-06-15   | 04-06-15 | Geroge Warrok, Katie Hills, Sarah Phillips |
+-----+------------+-----------+----------+------------+----------+--------------------------------------------+
  • It's definitely confusing. Try to come up with some sample data and the desired output, preferably in a form that helps reproducing it (CREATE TABLE and INSERT statements for the sample data). Also, check the formatting tools of the post editor. – dezso Jun 10 '15 at 8:56
  • I have updated it, making the create table script now. – Livewire Jun 10 '15 at 10:43
0

I splitted the problem into two basic SELECTs, one for members and the another one for guests and I join both later. MySql's GROUP_CONCAT aggregate funtion is very useful here too.

http://sqlfiddle.com/#!9/58786/1

SELECT 
TT1.day as day
,TT1.booking_id as booking_id
,TT1.member_id as member_id
,CASE 
  WHEN TT2.guest_ids IS NULL 
  THEN 0 
  ELSE TT2.guest_ids 
  END as guest_id
,TT1.start_date as start_date
,TT1.end_date as end_date
,CASE 
  WHEN TT2.guest_ids IS NULL
  THEN TT1.member_name
  ELSE CONCAT(TT1.member_name, ', ', TT2.guests_name)
  END as names
FROM
(
SELECT 
Min(day) as day
, booking_id
, member_id
, Members.id
, CONCAT(first_name,' ',last_name) as member_name
, start_date
, end_date   
FROM Bookings
INNER JOIN Members
ON Bookings.member_id = Members.id
WHERE member_id <> 0
GROUP BY booking_id, member_id, guest_id, start_date, end_date
) AS TT1
LEFT JOIN
(
SELECT 
booking_id
, GROUP_CONCAT(guest_id SEPARATOR ', ') as guest_ids
, GROUP_CONCAT(CONCAT(first_name, ' ', last_name) SEPARATOR ', ') as guests_name
FROM
(
SELECT
booking_id
, guest_id
FROM Bookings
WHERE guest_id <> 0
GROUP BY booking_id, guest_id
) AS T1
INNER JOIN Guests
ON T1.guest_id = Guests.id
GROUP BY booking_id
) AS TT2
ON TT1.booking_id = TT2.booking_id
ORDER BY TT1.day, TT1.booking_id
| improve this answer | |
  • Awesome, this worked perfectly. It's quite hard to understand what is exactly happening. How I interpret this is you have inner joined two tables as TT1 and inner joined another two tables as TT2. All in all you have done a fantastic job! Cheers – Livewire Jun 11 '15 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.