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, Jun 04 '14 at 06: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, Jun 04 '14 at 07:00
@Damien I agree, the table needs to be normalized. However it is a legacy database. — Kris Adidarma, Jun 04 '14 at 07:03

Andrius Naruševičius · Accepted Answer · 2014-06-04T06:54:37.237

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 04 '14 at 06:54

answered Jun 04 '14 at 06:32

Andrius Naruševičius

8,348
7
49
78

Thanks, this will work but I forgot to mention that some of the fields have a null value. how would you handle this? – Kris Adidarma Jun 04 '14 at 06:47
@KrisAdidarma I updated the queries regarding your additional request. – Andrius Naruševičius Jun 04 '14 at 06:55
great and simple solution. My thanks. which one is faster? the "IN" Solution or the "EXISTS" solution? – Kris Adidarma Jun 04 '14 at 07:01
According to the accepted answer in http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance `EXISTS` is faster :) – Andrius Naruševičius Jun 04 '14 at 07:08

sql check if rows are subset of another table

1 Answers1