CODEWITHSUNDEEP
sqlsqlite
SPYBUG96
SPYBUG96

Reputation: 1117

How to SELECT everything FROM a table WHERE column LIKE data from other table?

I've done something like this to get results one by one, but now I have a list of 300 companies that I need to get data from.

SELECT * FROM vault
WHERE defendant_client LIKE '%Purdue Pharma%';

The result of this is a list of everything where the company matches.

But I want to select everything from vault where defendant_client (a column in vault) matches or is similar to any entry in company which is in a different table.

SELECT * 
FROM vault
WHERE defendant_client LIKE (SELECT company FROM OpioidCompanies);

It works in the sense that it runs, but no results are returned, even though I know for a fact that there are a couple of matching companies and I should be able to get a few results back.

What am I doing wrong, and how do I get the results I need?

Upvotes: 0

Views: 1989

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I would do this using a correlated subquery:

SELECT v.* 
FROM vault v
WHERE EXISTS (SELECT 1
              FROM OpioidCompanies oc
              WHERE v.defendant_client LIKE '%' || oc.company || '%'
             );

This has two advantages over using JOIN:

  1. If a "vault" matches more than one company, there are no duplicates.
  2. The matching stops at the first match, which can be a gain for performance.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

After your most recent edit, you may want to try...

SELECT
    vault.*
FROM
    vault
INNER JOIN
    OpioidCompanies
        ON vault.defendant_client LIKE '%' || OpioidCompanies.company || '%'

If a simple %<blah>% isn't what you want, consider researching SOUNDEX() and related functions.

Upvotes: 2

Related Questions