Hello,
I need to build a SQL..i know the long way...need a short way

itemid Routeto
1 NB
1 IP
1 IQ
4 NB
4 IP
4 IQ
5 NB
5 JY
6 NB
6 AB
2 NB
3 IS

.
i want only those itemid which had routeto = NB and never had routeto = IQ or IP i.e 5 ,6 , 2

so i dont want 1,4 to ever come in the resultset!

Thks in advance

SELECT R1.itemid FROM tbRouteTo R1 WHERE R1.Routeto = 'NB' AND NOT EXISTS
(SELECT R2.itemid FROM tbRouteTo R2 WHERE R1.itemid = R2.itemid AND R2.Routeto IN ('IQ','IP'))

The first part of the query finds itemids that have a Routeto = 'NB' and don't have any matches in the second part (NOT EXISTS). It uses a table-alias: R1.

The second part finds itemids that have a Routeto = 'IQ' or 'IP' and the same itemid as the one in the first part (R1.itemid = R2.itemid) - this part uses the alias R2.

I hope this makes some kind of sense ;)

Thanks Jonax!