Union and Intersect - 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)

Union and Intersect

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2004-02-27 : 12:24:20

I have some queries in the format


select a, b, c
from t1

union

select d, e, f
from t2

union

select g, h, i
from t3

I want to compare the results of this query with the records in another table


select x,y,z
from t4

What I want to find is the records which are in table t4 and not in the results of the first union query and vice versa.

Any help or suggestions will be appreciated.

Thank you.
PKS

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-27 : 12:45:06

select *
from
(
select a, b, c
from t1
union
select d, e, f
from t2
union
select g, h, i
from t3
) a
full outer join
(
select x,y,z
from t4
) b
on a.a = b.x
and a.b = b.y
and a.c = b.z

The entries with nulls in the relevant columns are those where there is no matching record from that query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-02-27 : 12:57:18

Thus:

select *
from(
select a, b, c
from t1
union
select d, e, f
from t2
union
select g, h, i
from t3
) a
right outer join
(
select x,y,z
from t4
) b
on a.a = b.x
and a.b = b.y
and a.c = b.z
where
b.x is null

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sahu74
Posting Yak Master

100 Posts

Posted - 2004-02-27 : 14:50:12

Thank you nr and derrick. It worked.

Appreciate your help.
PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources