help with a query

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.SELECT Sale_ID
FROM Table
WHERE Sale_ID NOT IN (
SELECT Sale_ID
FROM Table
WHERE Sale_ObjectID = 100
)
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1150308761.602312.189200@.p79g2000cwp.googlegroups.com...
>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.
>|||VJ wrote:
> 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.
>
SELECT sale_id, sale_object_id
FROM sales
WHERE sale_id NOT IN (SELECT sale_id FROM sales WHERE sale_object_id = 100)|||Try,
select *
from t1 as a
where not exists (
select *
from t1 as b
where b.sale_id = a.sale_id and b.sale_object_id = 100
)
go
AMB
"VJ" wrote:

> 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.
>|||Maybe a WHERE NOT EXISTS clause could help you out...All of your
examples do have 100, so I added a couple of entries for sale_id of 7,
for which the sale_object_id is not 100.
SELECT sale_id, sale_object_id
FROM test t
WHERE (NOT EXISTS
(SELECT *
FROM test t2
WHERE t.sale_id = t2.sale_id AND
t2.sale_object_id = 100))
HTH,