Re: weird kind of join

[Diana Castillo]

This is the first one I tried, it works great, thanks
----- Original Message ----- 
From: "Gordon" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "'Diana Castillo'" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, September 29, 2004 8:05 PM
Subject: RE: weird kind of join


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]