Filtering Out Records on a Recordset

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)

Filtering Out Records on a Recordset

Author

Topic

treblesix
Starting Member

4 Posts

Posted - 2006-09-14 : 03:36:47

Hi all, I have the following problem, which I hope someone can help with.....

I have 2 tables. tblVacOrder and tblVacancies, with the following data...........

tblVacOrder
OrderID VacancyID
1 134
4 132
4 133
4 136
6 132
6 135
6 136

tblVacancies
VacancyID
132
133
134
135
136

I only want to return the vacancies not associated with the order i.e.
If the OrderID=1, then I only want to return VacancyID's 132,133,134,135,136
If the OrderID=4, then I only want to return VacancyID's 134 and 135
If the OrderID=6, then I only want to return VacancyID's 133 and 134

Anybody know a solution to this ?
Cheers,
Trebz

treblesix
Starting Member

4 Posts

Posted - 2006-09-14 : 03:42:51

Soz, that should be.........
I only want to return the vacancies not associated with the order i.e.
If the OrderID=1, then I only want to return VacancyID's 132,133,135,136
If the OrderID=4, then I only want to return VacancyID's 134 and 135
If the OrderID=6, then I only want to return VacancyID's 133 and 134

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 03:50:09

[code]
select *
from tblVacancies v
where not exists (select * from tblVacOrder x where x.OrderID = @OrderID and x.VacancyID = v.VacancyID)
[/code]

treblesix
Starting Member

4 Posts

Posted - 2006-09-14 : 03:54:58

Woah! That was a fast reply.
It works great!

Thanks for your help! / diolch yn fawr i ti'n helpu!