Solved

MySQL subquery using INSTR to exclude records

Posted on 2015-02-14
4
263 Views
Last Modified: 2015-02-15
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?
0
Comment
Question by:DanielAttard
  • 2
  • 2
4 Comments
 
LVL 12

Assisted Solution

by:FarWest
FarWest earned 500 total points
ID: 40610508
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
 

Author Comment

by:DanielAttard
ID: 40610908
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
 

Author Comment

by:DanielAttard
ID: 40610917
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
 
LVL 12

Accepted Solution

by:
FarWest earned 500 total points
ID: 40610921
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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This video teaches users how to migrate an existing Wordpress website to a new domain.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now