| Author |
 Topic  |
|
|
ch9862
Yak Posting Veteran
74 Posts |
 Posted - 04/17/2007 : 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)
Singapore
16746 Posts |
Posted - 04/17/2007 : 12:40:31
|
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
|
 |
|
|
dinakar
Flowing Fount of Yak Knowledge
USA
2507 Posts |
Posted - 04/17/2007 : 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
74 Posts |
Posted - 04/19/2007 : 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
USA
7423 Posts |
Posted - 04/19/2007 : 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
|
|
|
| |
Topic |
|
|
|
'Merging' two tables
