SQL Server - Join query

Question

Select No, A, B, C into #temp1 from Table1 Where month = 'Jan'
Select No, D, E, F into #temp2 from Table2 Where month = 'Jan'
Select No, G, H, I into #temp3 from Table3 Where month = 'Jan'

And the row count result in

#temp1 = 0
#temp2 = 0
#temp3 = 2

*note that the row count result will vary base on month selected

I want to combine the result of three table into one which the output as below:

No | A  | B | C | D | E | F | G | H | I
----------------------------------------

I've tried :

Select t1.No, t1.A, t1.B, t1.C, t2.D, t2.E, t2.F, t3.G, t3.H, t3.I
From #temp1 t1 
Left Join #temp2 t2 on t1.No = t2.No
Left Join #temp3 t3 on t1.No = t3.No

But I know the query is wrong because there will be no output since there is no result in table #temp1

Please help. Thanks

Answer 1

You can use Full Outer Join for your case. Try like this.

CREATE TABLE #temp1
  (
     no    INT,
     A     VARCHAR(10),
     B     VARCHAR(10),
     C     VARCHAR(10),
     MONTH VARCHAR(10)
  )

CREATE TABLE #temp2
  (
     no    INT,
     D     VARCHAR(10),
     E     VARCHAR(10),
     F     VARCHAR(10),
     MONTH VARCHAR(10)
  )

CREATE TABLE #temp3
  (
     no    INT,
     G     VARCHAR(10),
     H     VARCHAR(10),
     I     VARCHAR(10),
     MONTH VARCHAR(10)
  )

INSERT INTO #temp3
VALUES      (1,
             'G1',
             'H1',
             'I1',
             'Jan'),
            (2,
             'G2',
             'H2',
             'I2',
             'Jan')

    SELECT coalesce(t1.No,t2.no,t3.no) as No,
       t1.A,
       t1.B,
       t1.C,
       t2.D,
       t2.E,
       t2.F,
       t3.G,
       t3.H,
       t3.I
FROM   #temp1 t1
       FULL OUTER JOIN #temp2 t2
                    ON t1.No = t2.No
       FULL OUTER JOIN #temp3 t3
                    ON t1.No = t3.No 

Answer 2

If you want to show all result, your query can be bellow, but if your aim is different, please explain it a little more.

    Select t1.No, t1.A, t1.B, t1.C, t2.D, t2.E, t2.F, t3.G, t3.H, t3.I
    From #temp1 t1, #temp2 t2 , #temp3 t3