sql check if rows are subset of another table

Question

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

Please share with us the sql query that you have tried to build. — Aziz Shaikh
– Aziz Shaikh, Commented Jun 4, 2014 at 6:29
Once you start adding numbers to columns, it's usually a sign that your data model is wrong. There probably ought to be a separate table from product that contains a separate row for each required decal. This would mean that you only need two rows for product 5 and it's now easy, if and when the requirement comes along for some product to have a 4th decal. It also makes this query easy to write because it's now just a relational division. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 4, 2014 at 7:00
@Damien I agree, the table needs to be normalized. However it is a legacy database. — Kris Adidarma
– Kris Adidarma, Commented Jun 4, 2014 at 7:03

Andrius Naruševičius · Accepted Answer · 2014-06-04 06:54:37Z

2

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

edited Jun 4, 2014 at 6:54

answered Jun 4, 2014 at 6:32

Andrius Naruševičius

8,6289 gold badges53 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kris Adidarma Over a year ago

Thanks, this will work but I forgot to mention that some of the fields have a null value. how would you handle this?

Andrius Naruševičius Over a year ago

@KrisAdidarma I updated the queries regarding your additional request.

Kris Adidarma Over a year ago

great and simple solution. My thanks. which one is faster? the "IN" Solution or the "EXISTS" solution?

Andrius Naruševičius Over a year ago

According to the accepted answer in stackoverflow.com/questions/2065329/… EXISTS is faster :)

Collectives™ on Stack Overflow

sql check if rows are subset of another table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related