Combine two child tables based on parent

Question

Suppose I have 3 tables parent, child1 and child2.

The parent table may look as follows:

+--------+-----------+-----------+
| Column | Child1_Id | Child2_Id |
+--------+-----------+-----------+
| Name1  | 1         | 1         |
+--------+-----------+-----------+
| Name1  | 2         | 2         |
+--------+-----------+-----------+
| Name2  | NULL      | 3         |
+--------+-----------+-----------+
| Name1  | 4         | 4         |
+--------+-----------+-----------+
| Name2  | 5         | 5         |
+--------+-----------+-----------+

Currently I have tried this:

SELECT        Parent.*, Child_1.*, Child_2.*
FROM            Lead INNER JOIN
                     Child_1 ON Parent.Child1_Id = Child1.Id INNER JOIN
                     Child_2 ON Parent.Child2_Id = Child_2.Id 

The problem is, that I only get returned 4 records, but optimally it should return 5 records.

I believe it is because, Child1_Id is Null. However, I still want my records from Child2_Id.. Is there any way to achieve this?

Answer 1

You may change your code to replace join with left join since you need all the rows from left table, code could be written as:

SELECT        Parent.*, Child_1.*, Child_2.*
FROM          Lead AS Parent
          LEFT JOIN
              Child_1 ON Parent.Child1_Id = Child1.Id 
          LEFT JOIN
              Child_2 ON Parent.Child2_Id = Child_2.Id
;

Left Join - The LEFT JOIN clause allows you to query data from multiple tables. It returns all rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used.