CODEWITHSUNDEEP
sqlt-sql
cojimarmiami
cojimarmiami

Reputation: 659

how to join 4 tables data in one

I have 4 tables with the following info

Table 1 ------- date, route, qty1
table 2 ------- date, route, qty2
table 3 ------- date, route, qty3
table 4 ------- date, route, qty4

I need to join this 4 tables to create another one with the following result

date, route, qty1, qty2, qty3, qty4

all tables do not have necessarily the same routes or date, but if they do I will need the query match me that info under the same row. for example

Table 1 ------- date,    route,  qty1
                07/01/14   1      1
table 2 ------- date,    route, qty2
                07/01/14   1      2
table 3 ------- date,    route, qty3
                07/02/14   2      3
table 4 ------- date,    route, qty4
                07/03/14   1      4

results should be

date      route   qty1    qty2   qty3  qty4
07/01/14    1      1        2
07/02/14    2                      3
07/03/14    1                             4

could someone please help me?

Upvotes: 0

Views: 84

Answers (2)

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

You may want to outer join all the tables together.

http://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx

Example:

DECLARE @tbl1 TABLE
(
    d DATE,
    r INT,
    q1 INT
)

DECLARE @tbl2 TABLE
(
    d DATE,
    r INT,
    q2 INT
)

DECLARE @tbl3 TABLE
(
    d DATE,
    r INT,
    q3 INT
)

DECLARE @tbl4 TABLE
(
    d DATE,
    r INT,
    q4 INT
)


INSERT INTO @tbl1 SELECT '2014-07-01', 1, 1
INSERT INTO @tbl2 SELECT '2014-07-01', 1, 2
INSERT INTO @tbl3 SELECT '2014-07-02', 2, 3
INSERT INTO @tbl4 SELECT '2014-07-03', 1, 4

SELECT d = COALESCE(t1.d, t2.d, t3.d, t4.d)
    , r = COALESCE(t1.r, t2.r, t3.r, t4.r)
    , t1.q1
    , t2.q2
    , t3.q3
    , t4.q4
FROM @tbl1 t1
FULL OUTER JOIN @tbl2 t2
    ON t1.d = t2.d
    AND t1.r = t2.r
FULL OUTER JOIN @tbl3 t3
    ON t1.d = t3.d
    AND t1.r = t3.r
FULL OUTER JOIN @tbl4 t4
    ON t1.d = t4.d
    AND t1.r = t4.r

With results:

d           r   q1      q2      q3      q4
2014-07-01  1   1       2       NULL    NULL
2014-07-02  2   NULL    NULL    3       NULL
2014-07-03  1   NULL    NULL    NULL    4

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I would use union all with aggregation for this:

select date, route, sum(qty1) as qty1, sum(qty2) as qty2,
       sum(qty3) as qty3, sum(qty4) as qty4
from ((select date, route, qty1 as qty1, NULL as qty2, NULL as qty3, NULL as qty4
       from table1
      ) union all
      (select date, route, NULL as qty1, qty2 as qty2, NULL as qty3, NULL as qty4
       from table2
      ) union all
      (select date, route, NULL as qty1, NULL as qty2, qty3 as qty3, NULL as qty4
       from table3
      ) union all
      (select date, route, NULL as qty1, NULL as qty2, NULL as qty3, qty4 as qty4
       from table4
      )
     ) t
group by date, route
order by date, route;

EDIT:

If you want a new table (rather than just a query), use the into clause after the select.

Upvotes: 3

Related Questions