sql检查行是否是另一个表的子集

Question

你能帮我解决问题吗？我有一个tblProduct，这是一个材料清单，用于生产带有标记的物品。tblStock是库存的可用标记。我需要一个查询，以检查哪些产品可以根据现有的标记。产品只能在所有的贴花都可用的情况下才能生产。非常感谢你的进步

tblProduct

    Id      decal_1         decal_2     decal_3        
    1       111             121         144
    2       104             106         144
    3       121             144         163
    4       122             144         163
    5       (null)          163         191
    6       104             121         163

tblStock

Available_decal
111
121
144
163
191

期望输出

Id
1
3
5

请注意，数字5中有一个null。

Answer 1

应该由IN检查来完成

SELECT 
    * 
FROM 
    tblProduct 
WHERE 
    (decal_1 IS NULL OR decal_1 IN (SELECT Available_decal FROM tblStock))
    AND (decal_2 IS NULL OR decal_2 IN (SELECT Available_decal FROM tblStock))
    AND (decal_3 IS NULL OR decal_3 IN (SELECT Available_decal FROM tblStock))

另一种使用EXISTS的方法

SELECT 
    * 
FROM 
    tblProduct 
WHERE 
    (decal_1 IS NULL OR EXISTS (SELECT 1 FROM tblStock WHERE Available_decal = decal_1))
    AND (decal_2 IS NULL OR EXISTS (SELECT 1 FROM tblStock WHERE Available_decal = decal_2))
    AND (decal_3 IS NULL OR EXISTS (SELECT 1 FROM tblStock WHERE Available_decal = decal_3))