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
  • 39,256
  • 9
  • 74
  • 101
Lawrence Cooke
  • 1,567
  • 3
  • 26
  • 52
  • 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,242,037
  • 58
  • 646
  • 786