Philipp Weigel Philipp Weigel - 3 months ago 11
SQL Question

SQL Left JOINING 3 Tables

I have 1 Table:

Contacts (ID integer, Name text, ATT_ID integer)


My Table is filled with these values:

(1, 'Alice', 1)
(2, 'Bob', 1)
(3, 'Carol', 1)
(4, 'Dave', 4)
(5, 'Eve', 4)
(6, 'Frank', 6)


The goal is to join these Contacts ID with the paired ATT_ID.

This is my current SQL-Code:

SELECT t1.ID as ID, t1.Name , tt.Name , tt2.Name
FROM Contacts as t1

LEFT JOIN (
SELECT MIN(t2.ID), t2.Name, t2.ATT_ID FROM Contacts as t2
WHERE t2.ID <> t2.ATT_ID)
AS tt ON t1.ID = tt.ATT_ID

LEFT JOIN (
SELECT MAX(t3.ID), t3.Name, t3.ATT_ID FROM Contacts as t3
WHERE t3.ID <> t3.ATT_ID)
AS tt2 ON t1.ID = tt2.ATT_ID

WHERE t1.ID = t1.ATT_ID;


and my Result is:

Alice | Bob | null
Dave | null | Eve
Frank | null | null


But the desired result should look like this:

Alice | Bob | Carol
Dave | Eve | null
Frank | null | null


How can I accomplish this?

Answer

Assuming your RDBMS supports Window Functions...

This can be accomplished by generating a row number of for each sub group and only displaying the smallest row number.

This also assumes you have no more than 3 in each group.

The below is ORACLE specific but should work with MS SQL and Postgresql and DB2. It will not work with MySQL as window functions are not supported.

Note the with block as built will only work in ORACLE.

With contacts (ID, Name, ATT_ID) as (    
Select 1, 'Alice', 1 from dual union all
Select 2, 'Bob', 1 from dual union all
Select 3, 'Carol', 1 from dual union all
Select 4, 'Dave', 4 from dual union all
Select 5, 'Eve', 4 from dual union all
Select 6, 'Frank', 6 from dual)

--FROM HERE ON should work if window functions supported.

Select * from (
Select T1.Name N1, T2.Name N2, T3.Name N3, Row_Number() Over (partition by T1.ATT_ID order by T1.ID) rn
FROM Contacts T1
LEFT JOIN Contacts T2
 on T1.ATT_ID = T2.ATT_ID
and T1.ID < T2.ID
LEFT JOIN contacts T3
 on T2.ATT_ID = T3.ATT_ID
and T2.ID < T3.ID
and T1.ID < T3.ID) B
WHERE RN =1 

enter image description here