mysql - Joining Table with substring condition -

i have 2 tables :

create table t1( id int unsigned not null auto_increment, group varchar(3) not null, number int unsigned zerofill, used enum('yes','no') default );  id   group   number  used 1    '110'   00001   'yes' 2    '110'   00002   'yes' 3    '110'   00003   'yes' 4    '210'   00001   'yes' 5    '210'   00002   'yes' 6    '210'   00003   'yes' 7    '310'   00001   'yes'  create table t2( id int unsigned not null auto_increment, number varchar(13) default null);  id   number   1    '110-00001' 2    '110-00002' 3    '210-00002' 4    '310-00001' 

my first goal find every record t1 not used in t2:

query result :

id   group   number  used 3    '110'   00003   'yes' 4    '210'   00001   'yes' 6    '210'   00003   'yes' 

and 2nd goal set column used 'no':

id   group   number  used 3    '110'   00003   'no' 4    '210'   00001   'no' 6    '210'   00003   'no' 

i have tried use query :

  select * t1  left outer join t2               on t1.number = cast(substring(t2.number,8,5) int)            t2.id null; 

but mysql says:

you have error in sql syntax; check manual corresponds mysql server version right syntax use near 'int) tblpeserta.id null

is there better query problem?!

try

update t1 left join t2     on t1.`group` = left(t2.number, 3)    , t1.number = 0 + right(t2.number, 5)    set t1.used = 'no'  t2.id null 

and select

select *   t1 left join t2     on t1.`group` = left(t2.number, 3)    , t1.number = 0 + right(t2.number, 5)  t2.id null 

output:

| id | group | number | used | ------------------------------ |  3 |   110 |      3 |   no | |  4 |   210 |      1 |   no | |  6 |   210 |      3 |   no | 

sqlfiddle