?
Solved

MySQL subquery using INSTR to exclude records

Posted on 2015-02-14
4
Medium Priority
?
324 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 12

Assisted Solution

by:FarWest
FarWest earned 2000 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 2000 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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

764 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