0

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' );

And here is my SQL:

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

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?

DanielAttard
  • 3,467
  • 9
  • 55
  • 104
  • @Barmar - I am struggling with this. Would you mind taking a look at my fiddle and see what I am doing wrong? Many thanks. – DanielAttard Feb 15 '15 at 05:54
  • Your fiddle is the same as the code in the question. Have you tried implementing the solutions in the question I linked to? Just change the `=` condition to use `INSTR`, but the rest of the structure should be the same. – Barmar Feb 15 '15 at 05:56

0 Answers0