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 |
+-----+------------+-----------+----------+------------+----------+--------------------------------------------+
CREATE TABLE
andINSERT
statements for the sample data). Also, check the formatting tools of the post editor.