SQL query with a similar statement from two tables

Question

How can I execute a SQL query with a like statement from two different tables?

I need something like:

 select * from table1 where name like %table2.name 

This is not a general field, but a substring of a field in another table.

Answer 1

Edit

(original answer below)

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

To do this, you can use LIKE as part of the ON clause in the 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 

(For the SQL Server version of MySQL to add different semicolons, remove GO s and use ...LIKE concat('%', b.bar) instead.)

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

 one tone phone 

You do not need to concat if you can save the statement in b.bar .

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

 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 make you.

Original answer

(Perhaps no longer relevant)

It's hard to say what you're asking for, but here is an example of using LIKE to limit JOIN results:

 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' 

This will give you foo from someTable and bar from someOtherTable where the lines are connected using someField and foo starts with "SOMETHING" and bar ends with "SOMETHING ELSE".

Answer 2

Not particularly sure about the exact syntax, but here is the idea:

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

Answer 3

Based on @TJCrowder answer

Test tables

 create table #A ( id varchar(10) ) create table #b( number varchar(5) ) insert into #A values('123') insert into #A values('456') insert into #A values('789') insert into #A values('0123') insert into #A values('4567') select * from #A insert into #B values('12') insert into #b values('45') insert into #b values('987') insert into #b values('012') insert into #b values('666') 

Actual request

 select * from #a, #b where #a.id like '%' + #b.number + '%' 

Change the above query like

 select #A.* from #A,#B ... or select * from #a, #b where #a.id like #b.number + '%' -- one side match