access sql query need your help

[woro2006]

table structure is as follow

id field1 field2
1 yes $10
1 yes $20
1 $30
1 $40
2 $50
2 $60
.
.
.

I want to select 1) the records where field1=yes union with 2) the records where field1=Null but the id cannot be the same as 1).

Final Result set.
1 yes $10
1 yes $20
2 $50
2 $60

I can do this with a query like this
select id, field1, field2 from table where field1=yes
union
select id, field1, field2 from table where id NOT IN (select distinct id from table where field1=yes)

But my table is very large, so performance is an issue. Can someone shed some light where I can use some kinds of joins to speed things up?

 

[PHV]

First, be sure that id is indexed (allowing duplicates).
Then you may try this:
SELECT A.id, A.field1, A.field2
FROM yourTable AS A LEFT JOIN (
SELECT id FROM yourTable WHERE field1=yes GROUP BY id
) AS B ON A.id = B.id
WHERE A.field1=yes OR B.id IS NULL

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

 

[woro2006]

Thanks, PHV. It works.