SQL Select where a column contains one value but not another in another row

October 27, 2022

I have this table on the picture below.
I’m trying to select all rows with DestinationSystemID "11" where other rows with the same SupplierPartyID does not also have DestinationSystemID "16".

For example in the table below the following rows should be selected because they have rows with DestinationSystem 11, but not 16:

7300009017706
7300009043088
7330509000502

The following should not be selected because it has both DestinationSystemID 11 and 16:

7318270000006

I hope you understand what I’m trying to ask here.

I have tried searching for solutions but can not query the question correctly to find a solution.

>Solution :

NOT EXISTS works quite nicely for this:

SELECT {explicit t.columns}
  FROM dbo.YourTableName AS t
  WHERE t.DestinationSystemID = 11
    AND NOT EXISTS
    (
      SELECT 1 FROM dbo.YourTableName AS t2
        WHERE t2.SupplierPartyID = t.SupplierPartyID
          AND t2.DestinationSystemID = 16
    );