Joining Multiple Tables with Left Join in SQL

Feedback


Solution 1:

Do you want to use distinct

SELECT distinct * FROM TableA a
LEFT JOIN TableB b
       ON a.Id = b.Id
LEFT JOIN TableC c
       ON a.Id = c.Id


Solution 2:

Do it as a UNION, i.e.

 SELECT TableA.ID, TableB.ID, TableC.Id
   FROM TableA a
         INNER JOIN TableB b ON a.Id = b.Id
         LEFT JOIN TableC c ON a.Id = c.Id
 UNION
 SELECT TableA.ID, Null, TableC.Id
   FROM TableA a
         LEFT JOIN TableC c ON a.Id = c.Id

To retrieve the first and second rows of data, you can use two separate SELECT statements. However, without knowledge about the data you are working with, the process may require some adjustments to ensure accuracy.


Solution 3:


To achieve your goal of eliminating any multiple b.ids, logic needs to be applied. By utilizing

row_number()

to identify these multiples, and subsequently using

case

logic, the corresponding values can be made NULL.

select a.id, a.val,
       (case when row_number() over (partition by b.id, b.seqnum order by b.id) = 1 then val
        end) as bval
       c.val as cval
from TableA a left join
     (select b.*, row_number() over (partition by b.id order by b.id) as seqnum
      from tableB b
     ) b
     on a.id = b.id left join
     tableC c
     on a.id = c.id

A full join between B and C is not advisable as it may result in multiple rows. For instance, if B has 2 rows for an ID and C has 3, the result will be 6 rows. It seems like your objective is to get only 3 rows. To achieve this, you may consider an alternative approach such as:

select *
from (select b.*, row_number() over (partition by b.id order by b.id) as seqnum
      from TableB b
     ) b
     on a.id = b.id full outer join
     (select c.*, row_number() over (partition by c.id order by c.id) as seqnum
      from TableC c
     ) c
     on b.id = c.id and
        b.seqnum = c.seqnum join
     TableA a
     on a.id = b.id and a.id = c.id

The process involves listing items in both “B” and “C” categories, then merging them based on their position in the list. A full outer join is utilized to ensure that the resulting list is as long as the longest original list.

The final join involves both tables, allowing TableA to serve as a filter, thus excluding any additional IDs found in B and C from appearing in the results.

Frequently Asked Questions

Posted in Sql