CODEWITHSUNDEEP
mysqlsql-like
Spica
Spica

Reputation: 33

MySQL Multiple LIKE between multiple table

I had search for the solution but so far i didn't find it yet.

So here the problem, let say i have 2 table:

Table1

name | code
-------------------
a    | [a][b][c]
b    | [a][c][d]
c    | [a][e]

Table2

code | name
-------------------
a    | aaaa
b    | bbbb
c    | cccc
d    | dddd
e    | eeee

I want to do multiple select Table1.name and Table2.name where Table1.code LIKE '%b%' OR Table1.code LIKE '%e%'

so it should be result like this:

name | name
------------------
a    | aaaa
c    | cccc

I did try :

SELECT Table1.name, Table2.name 
FROM Table1, Table2 
WHERE Table1.code LIKE '%b%' OR Table1.code LIKE '%e%'

But it didn't work. I'm newbie on this, any help deeply appreciated :)

Upvotes: 3

Views: 5475

Answers (3)

GKV
GKV

Reputation: 501

Inorder to join 2 table using like function or instr function will

Using "like"

 **column_name1 like '%'column_name2'%'**

Using "instr"

 **instr(column_name1,column_name2)>0**

where column_name1 is referencing column and column_name2 is pattern you are searching

in your example try this

Using like

SELECT Table1_name, Table2_name FROM 
( SELECT Table1.name table1_name
        ,Table1.code table1_code
        ,Table2.name table2_name
        ,Table2.code table2_code
FROM Table1 JOIN Table2 ON  
(Table2.name LIKE '%'||Table1.name||'%') OR (Table1.code LIKE '%'||Table2.code||'%')
WHERE Table1.name=Table2.code
)  new1
 WHERE Table1_code LIKE '%b%' OR Table1_code LIKE '%e%'

Using INSTR

 SELECT Table1_name, Table2_name FROM 
( SELECT Table1.name table1_name
        ,Table1.code table1_code
        ,Table2.name table2_name
        ,Table2.code table2_code
FROM Table1 JOIN Table2 ON
INSTR(Table2.name,Table1.name)>0   OR INSTR(Table1.code,Table2.code)>0 
WHERE Table1.name=Table2.code
)  new1
 WHERE Table1_code LIKE '%b%' OR Table1_code LIKE '%e%'

Upvotes: 2

Jim
Jim

Reputation: 22656

SELECT t1.name, t2.name FROM 
Table1 t1 INNER JOIN Table2 t2 ON (t1.name = t2.code)
WHERE t1.code LIKE '%b%' OR t1.code LIKE '%e%'

Since this is an inner join this will only return rows from the first table if there is a matching code in the second. If you would want to display any matching rows from Table1 use LEFT JOIN

Upvotes: 2

Ricky Yoder
Ricky Yoder

Reputation: 571

If I'm right, something like this should work for you:

SELECT * FROM table1 AND table2 WHERE table1code LIKE '%e%'

Upvotes: 0

Related Questions