Solved

MySQL subquery using INSTR to exclude records

Posted on 2015-02-14
4
253 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

743 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

12 Experts available now in Live!

Get 1:1 Help Now