You might also try
FROM table_a
INNER JOIN table_b
ON table_b.code = substring_index(table_a.code,';',1)
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the
delimiter delim. If count is positive, everything to the left of the final
delimiter (counting from the left) is returned. If count is negative,
everything to the right of the final delimiter (counting from the right) is
returned.
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
This function is multi-byte safe.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 29, 2004 12:35 PM
To: Diana Castillo
Cc: [EMAIL PROTECTED]
Subject: Re: weird kind of join
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
