4 Tables Join with comman column

Transact-SQL
1

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.

Thanks

2

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...

3

table1(Date,A)
01-01-2016 10
02-01-2016 12

table2(Date,B)
01-01-2016 9
03-01-2016 5

table3(Date,C)
01-01-2016 7
02-01-2016 8

table4(Date,D)
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

Thanks

4 
; 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
5

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

6

Thanks dear it works fine