MySQL column value in LIKE query

Question

I have two tables in a database.

Both contain a related id but in a different format:

table1.field = 123456-12-34
table2.id = 123456

What Im trying to do is a join on table1.id = table2.field

so the query looks like:

select name from 
    table1 left join table2 on table1.field like table2-%
    where table2.flag='1' and DATEDIFF( now(), table1.timestamp ) > 2

I know this isn't correct, but how do i join two tables on fields where they are related but not the same?

Probably this will help you: how to use a like with a join in sql? — t.niese
– t.niese, Commented Jul 13, 2013 at 18:57

Gordon Linoff · Accepted Answer · 2013-07-13 18:58:00Z

3

You want to use concat() to create the pattern string:

select name
from table1 left join
     table2
     on table1.field like concat(table2.id, '-%')
where table2.flag='1' and DATEDIFF( now(), table1.timestamp ) > 2

answered Jul 13, 2013 at 18:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL column value in LIKE query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related