'Exclude strings that are in one table from another table

I have a table called tblExclusions. It has one column called ProductCode which contains words that I want to exclude in another table called tblSubscription with the column Product_Code. Product_Code contains strings that might contain words that I want to exclude using the tblExclusions data.

tblExclusions sample:

ProductCode (Header)

AllModels

WireFrame

Extension

tblSubscription sample:

Product_Code (Header)

C233 with AllModels

WireFrame Grid H456

Extension with G789

D132, F345, G567

DB07

Output should be: ProductCode

D132, F345, G567

DB07

I tried something I thought would work:

SELECT b.product_code
 FROM
      ttblExclusions a
       LEFT JOIN tblSubscription b
       ON  b.product_code  Not LIKE ("*" &  a.ProductCode & "*")

It returned records but it still contained rows with the strings I wanted to exclude. Is there a way to tweak this to do what I need?

Scott

sqlms-access


Solution 1:[1]

From explanation above seems we want to select product_code from tblSubscription table which are not available table(ttblExclusions).Below query will serve the purpose :

SELECT 
  a.product_code 
FROM 
  tblSubscription a 
where 
  a.product_code not in (
    select 
      distinct ProductCode 
    from 
      ttblExclusions
  );

Solution 2:[2]

I ended up using a recordset to loop through the tblExclusion data. Then used Instr to see if there was a match. If so, I ran an update query to flag a Delete column.

Do While Not rs.EOF
        strProdCode = rs("ProductCode")
        DoCmd.RunSQL ("UPDATE tblSubscription SET tblSubscription.[Delete] = 'Yes' WHERE (((InStr([Product_Code],'" & strProdCode & "'))>0));")
    rs.MoveNext
    Loop
Then I just deleted the flagged records.

Solution 3:[3]

Probably the most simple solution is the following:

WITH tblExclusions AS (
    SELECT 'AllModels' AS product_code
    UNION
    SELECT 'WireFrame'
    UNION
    SELECT 'Extension'
),
tblSubscription AS (
    SELECT 'C233 with AllModels' AS product_code
    UNION
    SELECT 'WireFrame Grid H456'
    UNION
    SELECT 'Extension with G789'
    UNION
    SELECT 'D132, F345, G567'
    UNION
    SELECT 'DB07'

)
SELECT s.product_code 
FROM tblExclusions e 
RIGHT JOIN tblSubscription s ON s.product_code LIKE '%' + e.product_code + '%'
WHERE e.product_code IS NULL

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 abhishek khanchi
Solution 2 ready4data
Solution 3 yoma

Related Questions