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

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.

CodePudding user response：

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
    );

Some will suggest NOT IN but I firmly believe this is an inferior solution and can lead newer users astray.

Here is why you should avoid NOT IN in general, even if in this specific case the column is not NULLable today. Readers will take that "working" solution and apply it other scenarios or discover a lot of broken queries when the column becomes NOT NULL later. More elaborate background in this blog post mentioned in a comment.

CodePudding user response：

NOT IN will also work with this as another option:

SELECT * FROM test a
WHERE DestinationSystemID = 11 
AND SupplierPartyID NOT IN (SELECT SupplierPartyID
                            FROM   test b
                            WHERE  a.SupplierPartyID = a.SupplierPartyID
                            AND b.DestinationSystemID = 16)

Fiddle here.

Note: Consider using NOT EXISTS over NOT IN if the target column accepts NULL values (See Accepted Answer above).