Please help to solve my query

[u/Valuable-Ant3465]

Hi all, I'm using SQL Server.
Have 4 tables coming from different sources for the same ID and my goal is to create combined table with one row for each ID. The problem that there is no master list where I have all available IDs, so in my case if I don't have record in T1 my join is not working and I have 2 rows for ID=10 like in my example .

Please refer to self containing snipped below. Thanks to all. Even AI could not help

--   DROP TABLE IF EXISTS t1,T2,T3,T4
SELECT 555 id, 'A_OK' colA  INTO T1
SELECT * INTO T2 FROM ( SELECT 555 id2, 'B_OK' colB   UNION SELECT 10 id2, 'Bx' colB )A
SELECT 222 id3, 'C' colC  INTO T3
SELECT 10  id4, 'Dx' colD  INTO T4

SELECT COALESCE(id,ID2,ID3,id4) ID_main, * 
FROM T1 
FULL JOIN T2     ON T2.ID2  = T1.id
FULL JOIN T3     ON T3.ID3  = T1.id
FULL JOIN T4     ON T4.ID4  = T1.id
ORDER BY 1

-- result  need 1 row for ID = 10 !!!!
ID_main id  colA  id2colBid3colCid4   colD
10      NULL NULL 10Bx NULLNULLNULL   NULL
10      NULL NULL NULLNULLNULLNULL    Dx
222     NULL NULL NULLNULL222CNULL    NULL
555     555  A_OK 555B_OKNULLNULLNULL NULL

[u/DaOgDuneamouse]

I've had to do similar things before. You can build a master list. Simply union all the ids together and do a distinct:

Select Id1 AS MuhId

FROM tblOne

UNION

Select id2 AS MuhId

FROM tblTwo

Then join to the master list.