4 Tables Join with comman column

I have 4 tables one comman column (Date and Qty) and another field

I want to join 4 tables with date as comman column.Some times qty will be null for particular date. I want to display all the dates even when one of table qty is Null.

Further details provided.


What are your table definitions? What have you tried? It would be nice to have example data both input and expected output.

You state one common column then talk about three, Date, Qty, and "another field".

FROM table1
INNER JOIN table2 ON ...
INNER JOIN table3 ON...
INNER JOIN table4 ON...

01-01-2016 10
02-01-2016 12

01-01-2016 9
03-01-2016 5

01-01-2016 7
02-01-2016 8

02-01-2016 5
03-01-2016 5

Desired result
Date A B C D
01-01-2016 10 9 7 5
02-01-2016 12
03-01-2016 5 8 5

Hope this helps


; with cte as
    select    Date, A, B = NULL, C = NULL, D = NULL
    from    table1
    union all
    select    Date, A = NULL, B, C = NULL, D = NULL
    from    table2
    union all
    select    Date, A = NULL, B = NULL, C, D = NULL
    from    table3
    union all
    select    Date, A = NULL, B = NULL, C = NULL, D
    from    table4
select    Date, MAX(A) AS A, MAX(B) AS B, MAX(C) AS C, MAX(D) AS D
from    cte
group by Date
1 Like

For another way see your other post: http://www.sqlservercentral.com/Forums/Topic1759920-392-1.aspx

Thanks dear it works fine