How to create a view from these 2 tables?

Question

Table 1 

bln thn qty1    
1   2014    10  
1   2014    20  
2   2014    30  
3   2014    40  
2   2014    50  
4   2014    60  



Table 2

bln thn qty2    
3   2014    200 
5   2014    400 
2   2014    100 
2   2014    500 
4   2014    300 
6   2014    600 

New View

bln thn qty1    qty2
1   2014    30  0
2   2014    80  600
3   2014    40  200
4   2014    60  300
5   2014    0   400
6   2014    0   600

From 2 tables at top, i'd like to create a view like table in the bottom.

Anyone would like to help ? :D thanks

Solved 4 years ago; look at this: http://stackoverflow.com/questions/3267360/data-from-two-tables-into-one-view — Marichyasana, Sep 30 '14 at 07:05
I have try using Union, but the result is like this : bln thn qty1 1 2014 10 1 2014 20 2 2014 30 3 2014 40 2 2014 50 4 2014 60 3 2014 200 5 2014 400 2 2014 100 2 2014 500 4 2014 300 6 2014 600 different from what i need :) — SaT, Sep 30 '14 at 07:19

JohnLBevan · Answer 1 · 2014-10-01T00:40:27.657

--create a new view called newview
create view NewView as
--if table1 has a record for this bln use it; otherwise take table 2's value
select coalesce(t1.bln,t2.bln) bln
--same for thn
, coalesce(t1.thn,t2.thn) thn
--take the sum of qty1 calculated below (max used just because we need an aggregate - they're all the same
, max(t1.qty1) qty1
--same for qty2
, max(t2.qty2) qty2
--select from the tables summing the quantities here (so we have a 1:1 match on the join / don't have to sum for every match)
from (select bln, thn, sum(qty1) as qty1 from table1 group by bln, thn) t1
full outer join (select bln, thn, sum(qty2) as qty2  from table2 group by bln, thn) t2
    on t1.bln = t2.bln
    and t1.thn = t2.thn
--group by the common fields so we get 1 record per value combination
group by t1.bln, t2.bln, t1.thn, t2.thn

SQLFiddle: http://sqlfiddle.com/#!6/708da/1

Possible typo: Line starting with "full outer"; should table2 be t2? Ditto for line starting with "from" — Marichyasana, Sep 30 '14 at 08:44

score 1 · Answer 2 · edited Oct 01 '14 at 00:38

I have made same Select as @JohnBevan but with some changes (not using Grouping on main select, also he forgot to name inner select columns)

DECLARE @table1 TABLE (bln INT, thn INT, qty1 INT)

INSERT INTO @table1 SELECT 1,2014,10
INSERT INTO @table1 SELECT 1,2014,20
INSERT INTO @table1 SELECT 2,2014,30
INSERT INTO @table1 SELECT 3,2014,40
INSERT INTO @table1 SELECT 2,2014,50
INSERT INTO @table1 SELECT 4,2014,60

DECLARE @table2 TABLE (bln INT, thn INT, qty2 INT)

INSERT INTO @table2 SELECT 3,2014,200
INSERT INTO @table2 SELECT 5,2014,400
INSERT INTO @table2 SELECT 2,2014,100
INSERT INTO @table2 SELECT 2,2014,500
INSERT INTO @table2 SELECT 4,2014,300
INSERT INTO @table2 SELECT 6,2014,600

CREATE VIEW NewView AS
SELECT COALESCE(T1.bln, T2.bln) AS bln
    , COALESCE(T1.thn, T2.thn) AS thn
    , COALESCE(T1.qty1, 0) AS qty1
    , COALESCE(T2.qty2, 0) AS qty2
FROM (
    SELECT bln, thn, SUM(qty1) AS qty1
    FROM @table1
    GROUP BY bln, thn
) AS T1
FULL JOIN (
    SELECT bln, thn, SUM(qty2) AS qty2
    FROM @table2
    GROUP BY bln, thn
) AS T2
    ON T1.bln = T2.bln
    AND T1.thn = T2.thn

SQLFiddle: http://sqlfiddle.com/#!6/a3e2b/1

Good answer; definitely better than mine. – JohnLBevan Oct 01 '14 at 00:36 — JohnLBevan, Oct 01 '14 at 00:36

How to create a view from these 2 tables?

2 Answers2