What Kind Of Join Do I Need To Use To Create One Table From Many?

I have 3 tables A, B and C. Each table has an ID and a value field. What kind of join do I use to consolidate these into one table with an ID field and columns value A, value B, va

Solution 1:

If the Ids are distributed across all the tables, union them together and group/aggregate on that:

SELECT Id, MAX(ValueA) AS ValueA, MAX(ValueB) AS ValueB, MAX(ValueC) AS ValueC
FROM (
    SELECT Id, ValueA, NULLAS ValueB, NULLAS ValueC
    FROM TableA
    UNIONALLSELECT Id, NULLAS ValueA, ValueB, NULLAS ValueC
    FROM TableB
    UNIONALLSELECT Id, NULLAS ValueA, NULLAS ValueB, ValueC
    FROM TableC
)
GROUPBY Id

If each Id is unique and exists once within each table:

SELECT TableA.Id, TableA.ValueA, TableB.ValueB, TableC.ValueC
FROM TableA
INNERJOIN TableB ON TableA.Id = TableB.Id
INNERJOIN TableC ON TableA.Id = TableC.Id

If each Id is unique, exists in TableA and is present either once or doesn't exist in the other tables (non-existant values get supplied as NULLs):

SELECT TableA.Id, TableA.ValueA, TableB.ValueB, TableC.ValueC
FROM TableA
LEFTOUTERJOIN TableB ON TableA.Id = TableB.Id
LEFTOUTERJOIN TableC ON TableA.Id = TableC.Id

If each Id exists uniquely in TableA but may exist multiple times in the other tables, use some sort of aggregation, like MAX or SUM:

SELECT TableA.Id, MAX(TableA.ValueA) AS ValueA, SUM(TableB.ValueB) AS ValueB, MIN(TableC.ValueC) AS ValueC
FROM TableA
INNER JOIN TableB ON TableA.Id = TableB.Id
INNER JOIN TableC ON TableA.Id = TableC.Id
GROUPBY TableA.Id

Solution 2:

Sounds like you want a left join. I also used a CTE to get all the ids from the various tables:

WITH ids as
(
    SELECT id from table1
    UNION
    SELECT id from table2
    UNION
    SELECT id from table3
)

SELECTdistinct ids.id, t1.col1, t2.col1, t3.col1 
FROM ids
LEFT JOIN Table1 as t1
ON ids.id = t1.id
LEFT JOIN Table2 as t2
ON ids.id = t2.id
LEFT JOIN Table3 as t3
ON ids.id = t3.id

Solution 3:

The following code uses FULL JOIN to return one row per ID whether or not the ID exists in all of the tables.

CREATETABLE #A
    ( Id INTNOTNULL
    , ValueVARCHAR(10) NOTNULL);

CREATETABLE #B
    ( Id INTNOTNULL
    , ValueVARCHAR(10) NOTNULL);

CREATETABLE #C
    ( Id INTNOTNULL
    , ValueVARCHAR(10) NOTNULL);

INSERTINTO #A (Id, Value)
VALUES (1, 'x'), (2, 'y');

INSERTINTO #B (Id, Value)
VALUES (2, 'a'), (3, 'b');

INSERTINTO #C (Id, Value)
VALUES (2, 'm');


SELECT Id =COALESCE(a.Id, b.Id, c.Id)
    , ValueA = a.Value
    , ValueB = b.Value
    , ValueC = c.Value
FROM #A a
FULLJOIN #B b
    ON a.Id = b.Id
FULLJOIN #C c
    ON a.Id = c.Id;