Bala,

You could try using NOT EXISTS. For example:

SELECT Id, LOC, AGENCY, BATCH
FROM tblRows
WHERE NOT EXISTS (
SELECT 1
FROM tblRows A
WHERE A.Loc = 'B'
AND A.Agency = tblRows.Agency
AND A.Batch = tblRows.Batch
)

The query would benefit from a (clustered) index on (Loc,Agency,Batch).

Hope this helps,
Gert-Jan


bala wrote:
>
> hey julian
>
> thanx for the pointer, i have tried different combination of outer
> joins but then it is kinda messy and not much difference in
> performance. the best bet is start from the scratch.
>
> regards
> bala