Query SQL with like operator from two tables

Question

How can I do a SQL query with the like operator from two different tables?

I need something like:

select * from table1 where name like %table2.name

It's not a common field but a substring of a field on another table.

Answer 1

Edit

(original answer is further down)

Your comment (and subsequent edit) completely changes the question.

To do that, you can use LIKE as part of the ON clause in a join:

CREATE TABLE a (foo varchar(254))
GO

CREATE TABLE b (id int, bar varchar(254))
GO

INSERT INTO a (foo) VALUES ('one')
INSERT INTO a (foo) VALUES ('tone')
INSERT INTO a (foo) VALUES ('phone')
INSERT INTO a (foo) VALUES ('two')
INSERT INTO a (foo) VALUES ('three')

INSERT INTO b (id, bar) VALUES (2, 'ne')
INSERT INTO b (id, bar) VALUES (3, 't')

SELECT a.foo
FROM a
INNER JOIN b ON a.foo LIKE '%' + b.bar
WHERE b.id = 2

(That's the SQL Server version; for MySQL, add in the various semicolons, remove the GOs, and use ...LIKE concat('%', b.bar) instead.)

That uses id = 2 to find bar = "ne" in table b, then prepends the % operator and uses it to filter results from a. Results are:

one
tone
phone

You won't have to do the concat if you can store the operator in b.bar.

Separately, I was surprised to find that this works (on SQL Server) as well:

SELECT foo
FROM a
WHERE foo LIKE (
    SELECT TOP 1 '%' + bar
    FROM b
    WHERE id = 2
)

...but the version using JOIN is probably more flexible.

That should get you going.

Original answer

(Arguably no longer relevant)

It's hard to tell what you're asking, but here's an example of using LIKE to limit the results from a JOIN:

SELECT a.foo, b.bar
FROM someTable a
INNER JOIN someOtherTable b
    ON a.someField = b.someField
WHERE a.foo LIKE 'SOMETHING%'
AND b.bar LIKE '%SOMETHING ELSE'

That will give you foo from someTable and bar from someOtherTable where the rows are related by someField and foo starts with "SOMETHING" and bar ends with "SOMETHING ELSE".

Answer 2

Not particularly sure about the precise syntax, but here's an idea:

select ... from (
    select ID, Foo as F from FooTable
    union all
    select ID, Bar as F from BarTable) R
where R.F like '%text%'