problem with data union from 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem with data union from 2 tables

Author

Topic

Gili
Starting Member

42 Posts

Posted - 2007-05-03 : 02:45:51

Hi,

i have a problem with union data from 2 tables here is example :

tbl_A:

code | plant | Month | year | val1 | val2 | val3
11 | 100 | 02 | 2007 | 500 | 600 | 700
11 | 100 | 03 | 2007 | 100 | 400 | 900

tbl_B

code | plant | Month | year | val4
11 | 100 | 02 | 2007 | 500
11 | 100 | 04 | 2007 | 100

after the join what i want to see is :

code | plant | Month | year | val1 | val2 | val3 | val4
11 | 100 | 02 | 2007 | 500 | 600 | 700 | 500
11 | 100 | 03 | 2007 | 100 | 400 | 900 | 0
11 | 100 | 04 | 2007 | 0 | 0 | 0 | 100

the result i get now with left outer join is :

code | plant | Month | year | val1 | val2 | val3 | val4
11 | 100 | 02 | 2007 | 500 | 600 | 700 | 500
11 | 100 | 03 | 2007 | 100 | 400 | 900 | 0

how can i get the requested result?
thanks in advance.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-03 : 04:38:07

declare @tt table (code int, plant int,Month int, year int,val1 int, val2 int, val3 int)
insert @tt
select 11, 100, 02, 2007, 500, 600, 700 union all
select 11, 100, 03, 2007, 100, 400, 900

declare @yy table (code int, plant int, Month int,year int, val4 int)
insert @yy
select 11, 100, 02, 2007, 500 union all
select 11, 100, 04, 2007, 100

select a.code, a.plant, a.month, a.year, max(a.val1) as Val1, max(a.val2) as Val2, max(a.val3) as Val3, max(a.val4) as Val4 from
(select code, plant, month, year, val1, val2, val3, 0 as val4 from @tt union
select code, plant, month, year, 0 as val1, 0 as val2, 0 as val3, val4 from @yy ) a
group by a.code, a.plant, a.month, a.year

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-03 : 05:36:47

[code]-- prepare sample data
declare @tt table (code int, plant int, Month int, year int, val1 int, val2 int, val3 int)

insert @tt
select 11, 100, 02, 2007, 500, 600, 700 union all
select 11, 100, 03, 2007, 100, 400, 900

declare @yy table (code int, plant int, Month int, year int, val4 int)

insert @yy
select 11, 100, 02, 2007, 500 union all
select 11, 100, 04, 2007, 100

-- show the expected output
select coalesce(t.code, y.code) as code,
coalesce(t.plant, y.plant) as plant,
coalesce(t.month, y.month) as month,
coalesce(t.year, y.year) as year,
coalesce(t.val1, 0) as val1,
coalesce(t.val2, 0) as val2,
coalesce(t.val3, 0) as val3,
coalesce(y.val4, 0) as val4
from @tt as t
full join @yy as y on y.code = t.code and y.plant = t.plant and y.month = t.month and y.year = t.year

select d.code,
d.plant,
d.month,
d.year,
sum(d.val1) as val1,
sum(d.val2) as val2,
sum(d.val3) as val3,
sum(d.val4) as val4
from (
select code,
plant,
month,
year,
val1,
val2,
val3,
0 as val4
from @tt

union all

select code,
plant,
month,
year,
0,
0,
0,
val4
from @yy
) as d
group by d.code,
d.plant,
d.month,
d.year[/code]

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 08:26:33

Definitely go with a UNION -- it is very rare that a FULL OUTER JOIN is a good solution. In fact, almost never! see: http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx for all the reasons why and alternatives.

pbguy has it pretty close, except for two things:

1) use UNION ALL instead of UNION, it is much more efficient.

2) Depending on your data, you may wish to use SUM() instead of MAX().

But overall, a UNION (ALL) is exactly what you need here and it's the way to go.

- Jeff
http://weblogs.sqlteam.com/JeffS

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 00:57:18

Thanks Jeff...your points are valid and helpful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources