Search code examples
sqlsql-servert-sqlinner-join

T-SQL Result Set - with 3 tables


Here is my code :

drop table if exists #Table_A
drop table if exists #Table_B
drop table if exists #Table_C

create table #Table_A
(
    [DATE] Date,
    TYPE    INT,
    QTY     INT
);

create table #Table_B
(
    [DATE] Date,
    TYPE    INT,
    QTY     INT
);

create table #Table_C
(
    [DATE] Date,
    TYPE    INT,
    QTY     INT
);

Insert into #Table_A VALUES ('2018-01-01', 1, 10)
Insert into #Table_B VALUES ('2018-01-01', 1, 5)
Insert into #Table_B VALUES ('2018-01-01', 2, 10)
Insert into #Table_B VALUES ('2018-01-01', 3, 10)
Insert into #Table_C VALUES ('2018-01-01', 1, 5)
Insert into #Table_C VALUES ('2018-01-01', 2, 10)
Insert into #Table_C VALUES ('2018-01-01', 3, 10)

Query :

select 
    A.[DATE], 
    SUM(CASE WHEN A.[TYPE] = 1 THEN A.QTY END)  A_QTY,
    SUM(CASE WHEN B.[TYPE] = 1 THEN B.QTY END)  B_QTY,
    SUM(CASE WHEN C.[TYPE] = 1 THEN C.QTY END)  C_QTY,
    SUM(CASE WHEN A.[TYPE] = 2 THEN A.QTY END)  A_QTY,
    SUM(CASE WHEN B.[TYPE] = 2 THEN B.QTY END)  B_QTY,
    SUM(CASE WHEN C.[TYPE] = 2 THEN C.QTY END)  C_QTY,
    SUM(CASE WHEN A.[TYPE] = 3 THEN A.QTY END)  A_QTY,
    SUM(CASE WHEN B.[TYPE] = 3 THEN B.QTY END)  B_QTY,
    SUM(CASE WHEN C.[TYPE] = 3 THEN C.QTY END)  C_QTY
FROM 
    #Table_A A 
    INNER JOIN #Table_B B ON A.DATE = B.DATE AND A.TYPE = B.TYPE
    INNER JOIN #Table_C C ON A.DATE = C.DATE AND A.TYPE = C.TYPE
GROUP BY A.[DATE]

Current Result:

DATE        |   A_QTY   |   B_QTY   |   C_QTY   A_QTY   B_QTY   C_QTY   A_QTY   B_QTY   C_QTY
2018-01-01  |   10      |   5       |   5       NULL    NULL    NULL    NULL    NULL    NULL

Expected Result:

DATE        |   A_QTY   |   B_QTY   |   C_QTY   A_QTY   B_QTY   C_QTY   A_QTY   B_QTY   C_QTY    
2018-01-01  |   10      |   5       |   5       NULL    10      10      NULL    10      10

Please let me know how I can get expected result. (I know currently its not working because of inner join).


Solution

  • The problem with your query is that, since it uses INNER JOINs, only records/date tuples that exists in all 3 tables are counted in.

    If there is a table that can will always have all (TYPE, DATE) tuples available (in your sample data, that would be Table_C), then you can use it a starting point and use LEFT JOINs :

    FROM #Table_C C 
    LEFT JOIN #Table_A A ON A.DATE = C.DATE AND A.TYPE = C.TYPE
    LEFT JOIN #Table_B B ON B.DATE = C.DATE AND B.TYPE = C.TYPE
    

    Else, you probably want to change your INNER JOINs to FULL OUTER JOINs :

    FROM #Table_A A 
    FULL OUTER JOIN #Table_B B ON A.DATE = B.DATE AND A.TYPE = B.TYPE
    FULL OUTER JOIN #Table_C C ON A.DATE = C.DATE AND A.TYPE = C.TYPE
    

    The FULL OUTER JOIN solution requires to adapt the SELECT and GROUP BY clauses, since the date could come from any of the 3 tables.

    Query :

    SELECT
        COALESCE(A.[DATE], B.[DATE], C.[DATE]) DATE,
        SUM(CASE WHEN A.[TYPE] = 1 THEN A.QTY END)  A_QTY,
        SUM(CASE WHEN B.[TYPE] = 1 THEN B.QTY END)  B_QTY,
        SUM(CASE WHEN C.[TYPE] = 1 THEN C.QTY END)  C_QTY,
        SUM(CASE WHEN A.[TYPE] = 2 THEN A.QTY END)  A_QTY,
        SUM(CASE WHEN B.[TYPE] = 2 THEN B.QTY END)  B_QTY,
        SUM(CASE WHEN C.[TYPE] = 2 THEN C.QTY END)  C_QTY,
        SUM(CASE WHEN A.[TYPE] = 3 THEN A.QTY END)  A_QTY,
        SUM(CASE WHEN B.[TYPE] = 3 THEN B.QTY END)  B_QTY,
        SUM(CASE WHEN C.[TYPE] = 3 THEN C.QTY END)  C_QTY
    FROM 
        #Table_A A 
        FULL OUTER JOIN #Table_B B ON A.DATE = B.DATE AND A.TYPE = B.TYPE
        FULL OUTER JOIN #Table_C C ON A.DATE = C.DATE AND A.TYPE = C.TYPE
    GROUP BY COALESCE(A.[DATE], B.[DATE], C.[DATE])
    

    This demo on DB Fiddle returns :

    DATE                | A_QTY | B_QTY | C_QTY | A_QTY | B_QTY | C_QTY | A_QTY | B_QTY | C_QTY
    :------------------ | ----: | ----: | ----: | ----: | ----: | ----: | ----: | ----: | ----:
    01/01/2018 00:00:00 |    10 |     5 |     5 |       |    10 |    10 |       |    10 |    10