SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 'Merging' two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ch9862
Yak Posting Veteran

76 Posts

Posted - 04/17/2007 :  12:37:03  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 04/17/2007 :  12:40:31  Show Profile  Reply with Quote

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



KH

Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/17/2007 :  12:57:32  Show Profile  Visit dinakar's Homepage  Reply with Quote
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/
Go to Top of Page

ch9862
Yak Posting Veteran

76 Posts

Posted - 04/19/2007 :  10:02:58  Show Profile  Reply with Quote
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!
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/19/2007 :  10:36:56  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000