try this (not tested):
FROM table_a
INNER JOIN table_b
ON table_b.code LIKE concat(table_a.code,';%')
or this:
FROM table_a
INNER JOIN table_b
ON table_b.code RLIKE concat('^',table_a.code,';')
http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html
http://dev.mysql.com/doc/mysql/en/Regexp.html
It's not going to be as quick as a direct lookup because of the CONCAT()
but at least we preserve the possibility of using an index for
table_b.code.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Diana Castillo" <[EMAIL PROTECTED]> wrote on 09/29/2004 12:39:40 PM:
> is there anyway to do a joint between a table that has codes like
> this 10004;XXX or DE;YYY
> with a table that has just the first part e.g 10004 or DE as the code
> There is no set length to the code , all I know is that it is the
> part before the semicolon.
> so, I can't say
> FROM table_a INNER JOIN table_b ON (table_a_code =
left(table_b.code,2))
> because I will only match the ones that have 2 character codes.
>
>
> Diana Castillo
> Global Reservas, S.L.
> C/Granvia 22 dcdo 4-dcha
> 28013 Madrid-Spain
> Tel : 00-34-913604039 Ext 216
> Fax : 00-34-915228673
> email: [EMAIL PROTECTED]
> Web : http://www.hotelkey.com
> http://www.destinia.com
