How to do FULL OUTER JOIN in MS SQL Server when columns don't share any parameter including primary key?

Question

I have many tables (let's call them single-parameter-tables) which include ID (primary key) and another parameter (2 columns in each table). I wish to join all of them in a joined_table consisting ID and param_1, param_2, ...., param_n columns. The joined_table is NOT NULL for ID column (primary key) and Nullable for other columns. When the parameters share the ID value, I can do the FULL OUTER JOIN normally and there's no problem. But when one parameter doesn't share primary key with any of the other parameters, I face a problem. Simply speaking, assume for ID 124 there is some value for param_3 from the third single_param-table but no other occurrence and value in other single-parameter-tables.

My code is as follows:

Insert into [joined_table]

(ID, param_1,param_2,param_3)

SELECT
       ID
      ,param1
      ,param2
      ,param3

FROM 
(
  SELECT 
        -- here if I write just "A.ID as ID" I will receive error of unfilled primary key column)
        COALESCE( A.ID, B.ID, C.ID) as ID 
        , A.param_1    as param1
        , B.param_2    as param2
        , C.param_3    as param3
  FROM 
     (
        (SELECT ID, param_1  FROM single_param_table_1) A

        FULL OUTER JOIN
        (SELECT IِِD, param_2  FROM single_param_table_2) B on A.ID= B.ID

        FULL OUTER JOIN 
        (SELECT ID, param_3 FROM single_param_table_3) C on A.ID = C.ID
        -- or:
        -- ISNULL(A.ID, B.ID)= C.ID
     )  

) as joined ;

The error message that I receive is as follows:

Violation of PRIMARY KEY constraint 'PK_joined_table'. Cannot insert duplicate key in object 'joined_table'.

It seems like parameter 3 is not completely separate from other parameters and in case it shares the key, repeated row is tried to be inserted into the table.

Ideally I wish to have the result joined_table as this:

 ID  |  param 1  |  param 2  |  param 3
=======================================
123  |    11     |    12     |   NULL
---------------------------------------
124  |   NULL    |   NULL    |    23

Answer 1

Your problem is that one or more table have duplicates.

In the meantime, your FULL JOIN logic is filtering out rows that you seem to want. You can simplify and improve the logic:

select coalesce(t1.id, t2.id, t3.id, t4.id, . . . ) as id,
       t1.param as param1,
       t2.param as param2,
       t3.param as param3,
       t4.param as param4,
       . . .
from single_param_table_1 t1 full join
     single_param_table_2 t2
     on t2.id = t1.id full join
     single_param_table_3 t3
     on t3.id = coalesce(t1.id, t2.id) full join
     single_param_table_4 t4
     on t4.id = coalesce(t1.id, t2.id, t3.id) full join
     . . .

That is, you need lots of use of coalesce() so the ids match across the tables.

I should note that standard SQL and most databases support the using clause which simplifies this logic. However, SQL Server does not support using.

That simplifies your logic. However, your issue is that one or more tables have duplicate ids.