'Merging' two tables - SQL Server Forums

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)

'Merging' two tables

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-04-17 : 12:37:03

I'm reading data from two tables, both holding similar data. The result is placed in one table, so I used union for it. There are minor differences between these tables (e.g. slightly different field names), but nothing major.

Because some of the data seems to be duplicated by mistake, I wanted to filter these out.

In other words, I have two tables:

declare @a table (id int, id2 int, d varchar(10))
insert	@a
select	1, 1, 'item a' union all
select	1, 3, 'item b' union all
select	2, 2, 'item c' union all
select	2, 4, 'item e'

declare @b table (id_1 int, id_2 int, descr varchar(10))
insert	@b
select	1, 1, 'item h' union all
select	2, 3, 'item i' union all
select	2, 4, 'item j' union all
select	2, 5, 'item k'

and I want to have one table from these, with the following records:

id_1 id_2  descr
1    1     item h   <- duplicate=from @b
1    3     item b
2    2     item c
2    3     item i
2    4     item j   <- duplicate=from @b
2    5     item k

Tables are joined on the first two columns. For duplicate records, I want to take values from table @b. How do I write such query?

I was thinking about selecting columns from both tables separately - i.e. id_1_a and id_1_b, etc. In this derived table I could have a 'marker' column indicating if record exists in @a and @b, and use case to select what I want. However: how do I arrive at such 'duplicat-less' derived table? Should I use some kind of join instead of union?

Thanks in advance for any suggestions

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-17 : 12:40:31

[code]
select id = coalesce(a.id, b.id_1),
id2 = coalesce(a.id2, b.id_2),
d = coalesce(b.descr, a.d)
from @a a full outer join @b b
on a.id = b.id_1
and a.id2 = b.id_2
order by id, id2
[/code]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-17 : 12:57:32

declare @a table (id int, id2 int, d varchar(10))
insert @a
select 1, 1, 'item a' union all
select 1, 3, 'item b' union all
select 2, 2, 'item c' union all
select 2, 4, 'item e'

declare @b table (id_1 int, id_2 int, descr varchar(10))
insert @b
select 1, 1, 'item h' union all
select 2, 3, 'item i' union all
select 2, 4, 'item j' union all
select 2, 5, 'item k'

select col1, col2 ,

col3 = case when exists (select descr from @b bb where bb.id_1 = z.col1 and bb.id_2 = z.col2) then (select top 1 descr from @b bb where bb.id_1 = z.col1 and bb.id_2 = z.col2)
else (select top 1 d from @a aa where aa.id = z.col1 and aa.id2 = z.col2)
end
from
(
select id col1, id2 col2 from @a
union all
select id_1 col1, id_2 col2 from @b
) z
group by col1,col2
order by 1,2

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-04-19 : 10:02:58

quote:
Originally posted by khtan

full outer join

Thanks - full outer join it is. Coalesce does not work for me in this case, because there will be plenty of legitimate NULL values. I think I will use case to test ids, and this will tell me which side of the join is present, to select the correct value.

Thanks to both of you!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-19 : 10:36:56

quote:
Originally posted by ch9862

quote:
Originally posted by khtan

full outer join

Thanks - full outer join it is. Coalesce does not work for me in this case, because there will be plenty of legitimate NULL values. I think I will use case to test ids, and this will tell me which side of the join is present, to select the correct value.

Thanks to both of you!

NO!!!! No full outer join -- avoid those whenever you can. A very simple UNION is all that you need. FULL OUTER JOINS aren't true relations, they just make a big mess and EVERY COLUMN needs to check for NULLs, and it makes your SQL less clear without a true "driving" table or recordset.

A simple UNION ALL gives you what you need, and it is short and efficient:


select id_1, id_2, coalesce(max(descr), max(d)) as Descr
from 
( select id as id_1, id2 as id_2, d, null as descr
  from @a
  union all
  select id_1, id_2, null, descr
  from @b
) x
group by id_1, id_2

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources