Solved

MySQL subquery using INSTR to exclude records

Posted on 2015-02-14
4
303 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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…

739 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