MySQL subquery using INSTR to exclude records

I am trying to create a MySQL subquery that will retrieve the records from table1, only if the records from table2 do not exist anywhere within the table1 record. Here is my schema:

CREATE TABLE table1 (
  CompanyName VARCHAR(20)
);

CREATE TABLE table2 (
  ExcludeName VARCHAR(20)
);

INSERT INTO table1 VALUES
( 'COMPANY AAA 111' ),
( 'COMPANY BBB 222' ),
( 'COMPANY CCC 333' ),
( 'COMPANY DDD 444' ),
( 'COMPANY EEE 555' );

INSERT INTO table2 VALUES
( 'BBB' ),
( 'DDD' );

Open in new window


And here is my SQL:

SELECT DISTINCT t1.CompanyName 
   FROM table1 t1
   JOIN table2 t2
       ON INSTR(t1.CompanyName, t2.ExcludeName ) = 0;

Open in new window


As shown in this SQL fiddle, I am not getting the correct result. I want the query to return only `COMPANY AAA 111`, `COMPANY CCC 333` and `COMPANY EEE 555`, but instead the query is returning all of the records. Why aren't the BBB and DDD records being excluded?
DanielAttardAsked:
Who is Participating?
 
FarWestCommented:
aha,
sorry I did not read the question carefully,
in this case this code can do what you need
SELECT DISTINCT t1.CompanyName FROM T1
WHERE NOT EXISTS (SELECT * FROM t2
                WHERE INSTR(t1.CompanyName ,t2.ExcludeName ) > 0 );

Open in new window

0
 
FarWestCommented:
Things are simpler than that,
you can just use EXISTS and Not EXISTS clause like this example
SELECT DISTINCT store_type FROM stores
  WHERE EXISTS (SELECT * FROM cities_stores
                WHERE cities_stores.store_type = stores.store_type);

Open in new window

0
 
DanielAttardAuthor Commented:
Thanks FarWest.  

I can see how this works for a complete text match, as in "New York = "New York", but what if I also wanted to test for partial matches like:

INSTR("New Jersey", "New" ) = 1
INSTR("New Hampshire", "New" ) = 1
INSTR("My New Value", "New" ) = 4
INSTR("Florida", "New" ) = 0
INSTR("Paul Newman", "New" ) = 6
INSTR("Texas", "New" ) = 0

What if I need to return the records where the value above is > 0 ?
0
 
DanielAttardAuthor Commented:
In the example you referred to, there is a relationship between the values in table1, and the values in table2, and you are testing to see if the values are equal.  In my situation, there is no relationship between the values in table1 and table2.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.