Join query - exclude dupliacte data from 2nd table

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)

Join query - exclude dupliacte data from 2nd table

Author

Topic

dunk
Starting Member

2 Posts

Posted - 2007-11-02 : 06:18:43

Hi,

I have what seems logically a straight forward query - but I'm not so sure!

I have 2 tables holding similar data - I need a query to get me all data from table 1, and all data from table 2 only if it is not already in table 1.

The common field is the week number

Table 1
week value
1 200
2 100
3 500
3 250
4 400

Table 2
week value
1 50
1 100
2 300
5 250
6 700

Should return:

week value
1 200
2 100
3 500
3 250
4 400
5 250
6 700

I want to use a join if possible rather than a subquery. Is it possible?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-02 : 06:30:50

[code]-- prepare sample data
declare @t1 table
(
[week] int,
value int
)

declare @t2 table
(
[week] int,
value int
)

insert @t1
select 1, 200 union all
select 2, 100 union all
select 3, 500 union all
select 3, 250 union all
select 4, 400

insert @t2
select 1, 50 union all
select 1, 100 union all
select 2, 300 union all
select 5, 250 union all
select 6, 700

-- Final Query
select distinct coalesce(t1.[week], t2.[week]) as [week], coalesce(t1.value, t2.value) as value
from @t1 t1 full join @t2 t2
on t1.[week] = t2.[week]
order by 1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-02 : 06:38:51

Another way is this:

-- Prepare sample data
declare @t1 table
(
	[week] int,
	value int
)

declare @t2 table
(
	[week] int,
	value int
)

insert @t1
select 1, 200 union all
select 2, 100 union all
select 3, 500 union all
select 3, 250 union all
select 4, 400

insert @t2
select 1, 50 union all
select 1, 100 union all
select 2, 300 union all
select 5, 250 union all
select 6, 700

-- Final Query
select [week], [value] from @t1
union all
Select t3.[week], t3.[value]
from 
@t2 t3 
where t3.[week] not in
(select t2.[week]
from @t2 t2 join @t1 t1 on t2.[week] = t1.[Week]
)
order by 1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dunk
Starting Member

2 Posts

Posted - 2007-11-05 : 07:14:51

Thanks that's great! Got me off and running a treat.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 08:02:59

[code]SELECT t1.[Week],
t1.Value
FROM @t1 AS t1

UNION ALL

SELECT t2.[Week],
t2.Value
FROM @t2 AS t2
WHERE NOT EXISTS (SELECT * FROM @t1 AS t1 WHERE t1.[Week] = t2.[Week])[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources