1

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?

t.niese
  • 36,631
  • 8
  • 65
  • 95
Lawrence Cooke
  • 1,525
  • 2
  • 24
  • 48
  • Probably this will help you: [how to use a like with a join in sql?](http://stackoverflow.com/questions/1386166/how-to-use-a-like-with-a-join-in-sql) – t.niese Jul 13 '13 at 18:57

1 Answers1

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
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709