hi!!!

the following is the sql which is veri slow cos of the 'Not In' clause,
would appreciate if u anyone can suggest any other way to bring about
the same result

SELECT Id, LOC, AGENCY, BATCH
FROM tblRows
WHERE tblRows.AGENCY NOT IN (SELECT DISTINCT B.AGENCY
FROM tblRows AS A, tblRows AS B
WHERE A.LOC = "B"
AND B.AGENCY = A.AGENCY
AND B.BATCH = A.BATCH)

tblRows
ID LOC AGENCY BATCH
1 B 1000 WAD
2 R 1000 WAD
3 B 1010 QAD
4 B 1020 WAD
5 R 1020 WAD
6 R 1030 RRR
7 I 1030 RRR
8 V 1030 RRR
9 B 1040 UIA
10 R 1040 UIA
11 I 1040 UIA
12 V 1040 UIA

the subquery is to return the rows with LOC = B. the above query as the
whole should return the rows where LOC <> b and also must exclude rows
belong to the LOC = B subset (that is for example the first two rows
with ID 1 and 2. the first row with ID 1 has LOC as B, Agency as 1000
and BATCH as WAD. the second row with Id 2 has LOC as R as the same
AGENCY and BATCH as first row with ID 1 so is the subset of first row.
similarly row with Id's 4 and 5.

the above query must return the following the rows (that is we can say
the orphan rows which doesn't have LOC AS B nor belongs to the B's
subset

6 R 1030 RRR
7 I 1030 RRR
8 V 1030 RRR

hope i am clear in my explanation and would appreciate if someone can
point me in the right direction.

regards
bala