具有IN运算符相反功能的查询

Question

我有一个查询来返回图像名，该名称位于目标I 1、2或3中。

SELECT AI.ACCESSORY_IMAGE_NAME, P.PART_CODE, PT.PUBLISHED_TARGET_ID, PT.TARGET_NAME FROM ASS.ACCESSORY_IMAGE AI
JOIN PUB.PUBLISHED_ITEM PI ON PI.PUBLISHED_OBJECT_ID =  AI.ACCESSORY_IMAGE_ID and PI.PUBLISHED_OBJECT_TYPE = 'ACCESSORY_IMAGE'
JOIN PRD.ACCESSORY A ON A.ACCESSORY_ID = AI.ACCESSORY_ID
JOIN PRD.PART P ON P.PART_ID = A.PART_ID
JOIN PUB.PUBLISHED_TARGET PT ON PI.PUBLISHED_TARGET_ID = PT.PUBLISHED_TARGET_ID AND PT.PUBLISHED_TARGET_ID in (1,2,3)
WHERE AI.ACCESSORY_IMAGE_NAME = 'fire_ext.jpg'

，它在目标id 1中发布图像时返回。

ACCESSORY_IMAGE_NAME    PART_CODE           PUBLISHED_TARGET_ID     TARGET_NAME
-------------------------------------------------------------------------------
fire_ext.jpg            PZ4AC-00EF0-EU      1                       DEALER_ACCESS

但我想返回目标的图像没有公布。在这种情况下是2和3。

预期输出

ACCESSORY_IMAGE_NAME    PART_CODE           PUBLISHED_TARGET_ID     TARGET_NAME
-------------------------------------------------------------------------------
fire_ext.jpg            PZ4AC-00EF0-EU      2                       INTERNET
fire_ext.jpg            PZ4AC-00EF0-EU      3                       INTRANET

"NOT IN“运算符显然不会返回任何行，因为图像是在目标1中发布的。

Answer 1

只需将最后一个联接更改为

JOIN PUB.PUBLISHED_TARGET PT ON PI.PUBLISHED_TARGET_ID <> PT.PUBLISHED_TARGET_ID

Answer 2

我不知道“出版”是什么意思。如果这意味着在published_target表中，那么使用一个left join并检查是否匹配：

SELECT AI.ACCESSORY_IMAGE_NAME, P.PART_CODE, PT.PUBLISHED_TARGET_ID, PT.TARGET_NAME FROM ASS.ACCESSORY_IMAGE AI
JOIN PUB.PUBLISHED_ITEM PI ON PI.PUBLISHED_OBJECT_ID =  AI.ACCESSORY_IMAGE_ID and PI.PUBLISHED_OBJECT_TYPE = 'ACCESSORY_IMAGE'
JOIN PRD.ACCESSORY A ON A.ACCESSORY_ID = AI.ACCESSORY_ID
JOIN PRD.PART P ON P.PART_ID = A.PART_ID
LEFT JOIN PUB.PUBLISHED_TARGET PT ON PI.PUBLISHED_TARGET_ID = PT.PUBLISHED_TARGET_ID AND PT.PUBLISHED_TARGET_ID in (1,2,3)
WHERE AI.ACCESSORY_IMAGE_NAME = 'fire_ext.jpg' and PT.PUBLISHED_TARGET_ID IS NULL;

同样的想法也适用于任何一张桌子。