Excluding records from table A which exist in Table B

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)

Excluding records from table A which exist in Table B

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-21 : 08:27:03

RICH writes "Here's what I've tried so far

SELECT C1.SEQNUM
FROM RB_PossNonDupe C1
LEFT OUTER JOIN RB_DefNonDupesSubset C2
ON C1.SEQNUM = C2.SEQNUM
WHERE C2.SEQNUM IS NULL

or
SELECT DISTINCT C1.SEQNUM
FROM RB_PossNonDupe C1,RB_DefNonDupesSubset C2
WHERE NOT EXISTS ( SELECT DISTINCT SEQNUM
FROM RB_DefNonDupesSubset C2
WHERE C1.SEQNUM = C2.SEQNUM
AND C2.SEQNUM IS NOT NULL
)

select count(*) from RB_PossNonDupe C1
where c1.seqnum not in ( select c2.seqnum
from RB_DefNonDupesSubset c2
where c2.seqnum = c1.seqnum )

I know for sure that there are records in both tables and table C2 is actually a subset of C1. But the result of all queriws is 0 records.

Any ideas?

Thanks
Rich"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-21 : 08:57:32

well if C2 is a subset of C1 then you need an inner join.


SELECT 	C1.SEQNUM
FROM 	RB_PossNonDupe C1
	INNER JOIN RB_DefNonDupesSubset C2 ON C1.SEQNUM = C2.SEQNUM

what you were doing is seeking records that are in C1 but not in C2.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources