Thanks for your quick reply Sean.

It is still giving me wrong results. it should give me the shipments which only and only has product type = 2. that means if any shipment id has product type id = 2 and also 3 then avoid those rows. there should not be product type = 3 in any of the other rows for that shipmentID.

as I said in my example

for example

ShipmentID ProductID ProductType Qty

1 1 2 10

1 2 2 5

1 3 3 1

2 1 2 10

2 2 2 5

The query should return only ShipmentID = 2, because that set only has producttypid = 2. I want to avoid those rows if it has at least one producttypeid = 3.

It should only return highlighted rows. As you can see product type = 2 is available in ShipmentID = 1 too but as it also has product type = 3 , I want to ignore that shipmentid