status not in

[chaoma]

Hi, could someone help me with this problem:

I have a table

Id status
1 60
1 70
1 90
2 30
2 60
2 80
3 40
3 40
3 60
3 70
4 60

I need to select all Id with status 30,40,50,60,70,80. If any ID do not contain one of these status, do not select these ID even if one or more already met the condition.

In this case, my result would be:

ID
2
3
4

ID=1 is not select because one of them contain status=90

I believe this query work,but very slow.

select distinct Id
from table
where Id not in
(
select ID
from table
where status not in (30,40,50,60,70,80)
)

I have over 30 millions records to search and have to join with other tables.

 

[PHV]

Perhaps this ?
SELECT DISTINCT A.ID
FROM table A LEFT JOIN (
SELECT ID FROM table WHERE status NOT IN (30,40,50,60,70,80)
) B ON A.ID = B.ID
WHERE B.ID IS NULL AND A.status IN (30,40,50,60,70,80)

Or this ?
SELECT DISTINCT A.ID
FROM table A
WHERE NOT EXISTS (SELECT * FROM table WHERE status NOT IN (30,40,50,60,70,80) AND ID = A.ID)
AND A.status IN (30,40,50,60,70,80)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chaoma]

Thank you. I'll try it tomorrow at work.

 

[chaoma]

Sorry, my data source doesn't allow left, right join. It only like equal join.

Also, it doesn't perform very well with subquery. Is there anyway to replace subquery with equal join. Thanks.

 

[fredericofonseca]

what database are you using? And what do you call your "data source"?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

So, don't post in the ANSI SQL Forum.
Search a more suitable forum for you in the Find a Forum search box on top of this page.

 

[chaoma]

I don't know what database I am using, but I know it there.

Thank you anyway.