exclude rows from query - 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)

exclude rows from query

Author

Topic

Danny__T
Starting Member

27 Posts

Posted - 2004-09-15 : 05:25:45

I have the following view and table

vwMatched
userId
jobId
otherFields

tblExclude
userId
jobId

the exclude table is a lookup table of jobs that have been matched to a user that they are not interested in. How can I query my view to retrieve ALL distinct userId and job combinations UNLESS that user and that job combination is in the exclude table?

Cheers,

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 05:33:43

something like:

select t1.*
from vwMatched t1
left join tblExclude t2 on t1.UserId = t2.UserId and t1.jobId = t1.jobId
where t2.jobid is null and t2.userid is null

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

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-15 : 05:34:36

SELECT DISTINCT
vm.userId,
vm.jobId
FROM vwMatched AS vm
LEFT JOIN tblExclude AS e
ON vm.userId = e.userId
AND vm.jobId = e.jobId
WHERE e.userId IS NULL

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources