0

I have a query that retrieves a list of names. I need to modify the query by adding a subquery that will exclude t1.name if it appears within t2.exclude. I need some help with constructing the subquery, which I understand will go into the WHERE clause below:

SELECT t1.name
FROM t1
WHERE *** t1.name is not in t2.exclude ***

What is the correct syntax for the subquery that I need? Thanks.

DanielAttard
  • 3,467
  • 9
  • 55
  • 104
  • 1
    There are a couple ways to do this using `not exists`, `not in` or `outer join null` checks. However, without understanding your table structures, it's difficult to provide the correct answer. – sgeddes Feb 14 '15 at 01:31

1 Answers1

0

try this:

SELECT t1.name
FROM t1
WHERE t1.name NOT IN (SELECT t2.name from t2)

you'll exclude all t1.name that exist in t2.name

EDIT 1

Doing some tests i end up in a very weird query, but it works, here you go:

SELECT * from t1
WHERE t1.name NOT IN (
    SELECT t1.name FROM t1, t2
    WHERE t1.name LIKE CONCAT('%', t2.name ,'%')
)

if you're wondering how it works, it does an inverse whitelist check

  1. Select ALL names from Both tables (cartesian product problem here)
  2. get names from t1 that LOOKS LIKE t2 names (whitelisting)
  3. then use that list of t1 names to filter the original t1 result. (blacklisting)

tldr; you do a While-black-list over the two tables.

Check out the test on SQL Fiddle

josegomezr
  • 888
  • 4
  • 15