CODEWITHSUNDEEP
sqlms-access
MetalPesto
MetalPesto

Reputation: 49

Use wildcards that are stored in table columns in SQL-queries with MS Access

I want to join two tables in Access based on different wildcards for different rows.

The first, table1, contains rows with different wildcards and table2 contains the column that should be matched with the wildcards in table1.

I imagine the SQL code to look like:

SELECT * 
FROM table2 
LEFT JOIN table1 
ON table2.subject LIKE table1.wildcard

The tables look like this: https://i.sstatic.net/hT72p.jpg

The third pictures shows the result that I want.

How do I execute the join or is there an alternative?

Upvotes: 1

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I don't think MySQL support non-equality conditions for JOINs. So, you can do this as:

SELECT *                  -- first get the matches
FROM table2 as t2,        -- ugg, why doesn't it support CROSS JOIN
     table1 as t1
WHERE t2.subject LIKE t1.wildcard
UNION ALL
SELECT *                  -- then get the non-matches
FROM table2 as t2 LEFT JOIN  
     table1 as t1
     ON 1 = 0             -- always false but gets the same columns
WHERE NOT EXISTS (SELECT 1
                  FROM table1 as t1
                  WHERE t2.subject LIKE t1.wildcard
                 );

Upvotes: 2

Related Questions