0

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?

1
  • 2
    Have you tried with LEFT JOIN?
    – McNets
    Oct 8, 2020 at 17:31

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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