CODEWITHSUNDEEP
sqlsql-server-2008
Nugeswale
Nugeswale

Reputation: 63

Group where clause use

ItemNo  ItemMarker
100007  3
100007  4
100007  5
100007  6

I have a query and I want to exclude all item no that has ItemMarker 6, which means I dont want to see 3,4,5.

When I use a filter where ItemMArker = 6, it is still returning 3,4,5 rows but I want any Item that has marker 6 to all be excluded regardless of the ItemMarker no.

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Use not exists:

select t.*
from t
where not exists (select 1 
                  from t t2
                  where t2.itemno = t.itemno and t2.itemmarket = 6
                 );

Upvotes: 1

Related Questions