sql - 选择 2 列中没有特定值的记录

Question

我需要选择一些在 2 列中没有特定值的行。目前我正在通过执行以下操作为单个列执行此操作:

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

但现在我需要过滤掉不同时包含 2 个列值的行:ItemID 和 ChildItemID

我将如何完成这个？

Answer 1

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

或

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
                 )