Mysql – Combining data from 3 tables depending values stored in 1 table

join;MySQL

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 |
+-----+------------+-----------+----------+------------+----------+--------------------------------------------+

Best Answer

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
Related Question