Combine 2 table with both like and unlike columns-CodePudding

Wondering how I would join tables 1 & 2 to get wanted result query. I have tried to union left and right joins, but the like columns are not lining up as I would hope.

Table 1

|Date|ClientID|ClientName|Revenue|Cost1A|Cost1B|Cost1C|
|----|--------|----------|-------|------|------|------|
|12/13/2021|001|ClientA|100|10|10|0|
|12/13/2021|001|ClientA|250|10|10|0|
|12/13/2021|002|ClientB|2500|10|15|1000|

Table 2

|Date|ClientID|ClientName|Revenue|Cost2|
|----|--------|----------|-------|-----|
|12/13/2021|003|ClientC|100|10|
|12/13/2021|003|ClientC|250|10|
|12/13/2021|001|ClientA|25|10|

Wanted Result
|Date|ClientID|ClientName|Revenue|Cost1A|Cost1B|Cost1C|Cost2|
|----|--------|----------|-------|------|------|------|-----|
|12/13/2021|001|ClientA|100|10|10|0|
|12/13/2021|001|ClientA|250|10|10|0|
|12/13/2021|002|ClientB|2500|10|15|1000|
|12/13/2021|003|ClientC|100||||10|
|12/13/2021|003|ClientC|250||||10|
|12/13/2021|001|ClientA|25||||10|

CodePudding user response：

Use placeholder value for missing column in UNION - I use Null.

SELECT [Date], ClientID, ClientName, Revenue, Cost1A, Cost1B, Cost1C, Null AS Cost2, "T1" AS Src FROM [Table 1]

UNION SELECT [Date], ClientID, ClientName, Revenue, Null, Null, Null, Cost2, "T2" FROM [Table 2];

Use UNION ALL to retain duplicate records.