SQL union & Subquery - 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

SQL Server Development (2000)

SQL union & Subquery

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-13 : 10:34:20

I have the following union:

select *
from Snapsraw
where (Branchstate = 'Arizona') and sicid is not null and sicid <> '99' and sectorid <> '100' and (monthend = '20060801')
union
SELECT *
FROM SnapsRaw
WHERE (Branchstate = 'Arizona') and (sectorid = '100' or sicid is null) and (Sicdescription is null) and (monthend = '20060801')

I want to than say, take those accountnumbers that come back in the results (1449 rows)and show me the accounts that show up in the following query but not in the union query

select accountid
from Snapsraw
where (Branchstate = 'Arizona') and (monthend = '20060801')

This query comes back with (1509) so basically I want to know the 60 records that dont show up in my union, just not sure how to build the subquery using the union.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-13 : 10:43:06

select a.accountid
from
(select accountid
from Snapsraw
where (Branchstate = 'Arizona') and (monthend = '20060801')) b
left join
(
select *
from Snapsraw
where (Branchstate = 'Arizona') and sicid is not null and sicid <> '99' and sectorid <> '100' and (monthend = '20060801')
union
SELECT accountid
FROM SnapsRaw
WHERE (Branchstate = 'Arizona') and (sectorid = '100' or sicid is null) and (Sicdescription is null) and (monthend = '20060801')
) b
on a.accountid = b.accountid
where b.accountid is null

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources