help with qurey - 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)

help with qurey

Author

Topic

vishalj
Starting Member

32 Posts

Posted - 2006-06-14 : 14:12:00

i have a table with

sale_id , sale_object_id
for each sale_id there can be multiple sale_object_id's but not 2 same sale_object_id in each sale

sale_id sale_object_id

1 100
1 120
1 140
2 100
2 120
3 100
3 140
4 140
4 100
5 100
6 120
6 100

now I want to write a query to find out sale_id where sale_object_id was say not = 100. If I do

select * from sales where sale_object_id <>100 i still get 1 and 2 in the result.

But I want to eliminate 1 & 2 and all other id's which even have
100 in them.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-14 : 15:06:23

[code]
select
a.*
from
sales a
where
a.sale_id not in
(
select
b.sale_id
from
sales b
where
b.sale_object_id = 100
)
[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources