CODEWITHSUNDEEP
mysqlsqlms-access
Kris Adidarma
Kris Adidarma

Reputation: 45

sql check if rows are subset of another table

will you help my problem? I have a tblProduct which is a bill of material for producing items with decal. tblStock is the available decal that is in stock. I need a query to check which product can be manufactured based on the available decal. The product can only be produced if all the decals are available. Thanks so much in advanced

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

Desired Output

Id
1
3
5

Note that there is a null in number 5. this complicates the matter

Upvotes: 1

Views: 2727

Answers (1)

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

An IN check should do it

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

Another way with 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))

Upvotes: 2

Related Questions