CODEWITHSUNDEEP
sqlsql-servert-sql
Lee Vincent
Lee Vincent

Reputation: 35

SQL Server : joining/ merging tables

Assume there are three total tables A,B, and C

Each table contains following attributes:

Using join operators, I'm trying to achieve a single table view with:

Result table:

"date", "id", "tv_sales_amt", "newspaper_sales_amt", "radio_sales_amt"

Desired look of the result table:

date       id    tv_sales_amt  newspaper_sales_amt   radio_sales_amt 
--------------------------------------------------------------------
20190101   012C     2000             1850            NULL 
20190102   102D     1000             NULL            1300 
.
.
.

Here are some queries that I've tried:

Query #1:

SELECT 
    A.date, A.id, tv_sales_amt, newspaper_sales_amt, radio_sales_amt 
FROM A 
INNER JOIN B ON A.id = B.id 
INNER JOIN C ON A.id = C.id

Using inner inner-join, I get duplicated values, which is understandable but is not what I'm looking for.

Query #2:

SELECT 
    A.date, A.id, tv_sales_amt, newspaper_sales_amt, radio_sales_amt 
FROM A 
FULL OUTER JOIN B ON A.id = B.id 
FULL OUTER JOIN C ON A.id = C.id

Since inner-join would only return results from Table B and C (newspaper_sales_amt and radio_sales_amt) that intersects with Table A, I tried full-outer-join in the hopes that it would give me overview of entire results, even though it includes null values.

With both options that I've tried, I wasn't able to get the expected result (Desired look described above).

Would someone be able to tell me what I'm doing wrong here?

I'm using the latest version of SQL Server Management Studio.

I do know that there must be lots of null values if I were to take an overview of tv_sales_amt, newspapers_sales_amt, and radio_sales_amt, but currently, there are no null values but with duplicates.

Upvotes: 0

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Full outer joins are tricky. I would suggest:

SELECT COALESCE(A.DATE, B.DATE, C.DATE) as date,
       COALESCE(A.ID, B.ID, C.ID) as id,       
       A.tv_sales_amt, B.newspaper_sales_amt, C.radio_sales_amt 
FROM A FULL JOIN
     B
     ON B.id = A.id AND
        B.date = A.date FULL JOIN
     C
     ON C.id = COALESCE(A.id, B.id) AND
        C.date = COALESCE(B.date, B.date);

I find that using COALESCE() in the ON clause simplifies adding more conditions. From a performance perspective, both COALESCE() and OR are pretty bad.

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

Your description implies that date also should be the part of joins.

select coalesce(a.date, b.date, c.date) [date],
   coalesce(a.id, b.id, c.id) id, 
   a.tv_sales_amt, b.newspaper_sales_amt, c.radio_sales_amt
from tableA a
full join tableB b on a.id = b.id and a.date = b.date
full join tableC c on (c.id = b.id and c.date = b.date) or 
                      (c.id = a.id and c.date = a.date);

EDIT: This is DbFiddle demo. Try removing coalesce() for date or id (removing in only one of them helps to see it better).

EDIT: Maybe this shows the need for coalesce() better:

select coalesce(a.date, b.date, c.date) [date],
   a.id idA, b.id idB, c.id idC, 
   a.tv_sales_amt, b.newspaper_sales_amt, c.radio_sales_amt
from tableA a
full join tableB b on a.id = b.id and a.date = b.date
full join tableC c on (c.id = b.id and c.date = b.date) or 
                      (c.id = a.id and c.date = a.date);

Upvotes: 3

Related Questions