Combining a parent table with multiple child tables

join;query

I am struggling to find a way to combine multiple tables (through a view) to get the desired result.

I've found this Stack Overflow Q & A, which got me startied, but I can't get the result I need.

There is a parent table and multiple child tables, which have a child_id that corresponds to a parent_id. However, each child has some matching fields and some unique fields, which I'd like to combine all together.

Parent

+-----------+----------+
| parent_id | name     |
+-----------+----------+
|         1 | Bob      |
|         2 | Peter    |
|         3 | Alice    |
+-----------+----------+

Child 1

+----------+------------+-------+
| child_id | date       | color |
+----------+------------+-------+
|        1 | 2017-07-20 | red   |
|        3 | 2017-07-24 | blue  |
+----------+------------+-------+

Child 2

+----------+------------+--------+
| child_id | date       | lenght |
+----------+------------+--------+
|        2 | 2017-07-21 | 185    |
+----------+------------+--------+

Combined desired result

+-----------+-------+----------+------------+-------+--------+
| parent_id | name  | child_id | date       | color | lenght |
|         1 | Bob   |        1 | 2017-07-20 | red   | NULL   |
|         2 | Peter |        2 | 2017-07-21 | NULL  | 185    |
|         3 | Alice |        3 | 2017-07-24 | blue  | NULL   |
+-----------+-------+----------+------------+-------+--------+

Best Answer

You need outer joins. Specifically to LEFT JOIN the parent table with each child:

  parent
LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id

and then SELECT from the appropriate tables. Columns that are in both children tables can be combined with COALESCE(). The order of the expressions inside the function matter only if there are cases where an id appears in both childen.

The query will be something like:

SELECT 
    p.parent_id, p.name,
    COALESCE(c1.child_id, c2.child_id) AS child_id,
    COALESCE(c1.date, c2.date) AS date, 
    c1.color, 
    c2.lenght
FROM
    parent AS p
  LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
  LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id ;

Related Solutions

Sql-server – Querying multiple tables

This will get you the Status Description:

SELECT s.SR_Service_RecID, SR_Status.Description, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s 
    INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
    INNER JOIN SR_Status ON s.ST_Status_RecID = SR_Status.SR_Status_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
                        FROM SR_Type t 
                        INNER JOIN SR_Board b
                            ON t.SR_Board_RecID = b.SR_Board_RecID
                        WHERE t.description LIKE @Type
                           AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
                          FROM SR_Status
                          WHERE Closed_Flag = 0)

To add the schedule, you'd need to add a key field from the schedule table to the SR_Service table.

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

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