CODEWITHSUNDEEP
sqlsql-server
Slee
Slee

Reputation: 28278

select records that don't have certain values in 2 columns

I need to select some rows that don't have certain values in 2 columns. Currently I am doing this for a single column by doing the following:

SELECT *
FROM MyTable
WHERE    (ManufacturerID = @ManufacturerID)
    AND ItemID NOT IN(
        SELECT ItemID FROM UpdateMyTable WHERE ManufacturerID=@ManufacturerID
    )

But now I need to filter out rows that don't contain 2 column values at the same time: ItemID and ChildItemID

How would I accomplish this?

Upvotes: 3

Views: 2429

Answers (2)

Brian Knight
Brian Knight

Reputation: 5041

You could use a JOIN instead of a NOT IN.

SELECT *
FROM MyTable t
INNER JOIN UpdateMyTable u ON t.ManufacturerId = u.ManufacturerId
WHERE t.ManufacturerId = @ManufacturerId
AND t.ItemId != u.ItemId
AND t.ItemId != u.ChildItemId

Upvotes: 1

MatBailie
MatBailie

Reputation: 86798

SELECT
  *
FROM
  MyTable                                 AS data
LEFT JOIN
  (SELECT x, y, z FROM UpdateMyTable)     AS check
    ON  data.x = check.x
    AND data.y = check.y
    AND data.z = check.z
WHERE
  x = @x
  AND check.x IS NULL


OR

SELECT
  *
FROM
  MyTable                                 AS data
WHERE
  x = @x
  AND NOT EXISTS (
                  SELECT
                    *
                  FROM
                    UpdateMyTable        AS check
                  WHERE
                      data.x = check.x
                  AND data.y = check.y
                  AND data.z = check.z
                 )

Upvotes: 5

Related Questions