|
|
Posted by no.mail.pls on 03/21/06 06:29
"Erwin Moller"
<since_humans_read_this_I_am_spammed_too_much@spamyourself.com> wrote in
message news:441a7f1d$0$11065$e4fe514c@news.xs4all.nl...
> no.mail.pls wrote:
>
>> Hiya,
>>
>> How do i retreive fields with similar values from 2 tables?
>>
>> I tried to use
>> (1) "SELECT * FROM $table1 as o , $table2 as p WHERE o.name like
>> '%p.name%'";
>> but it retrieves nothing at all.
>>
>> (2) "SELECT * FROM $table1 as o , $table2 as p WHERE o.name like p.name";
>> will retrieve exact matches only, but this is not what i want.
>>
>> (3) SELECT * FROM $table1 as o , $table2 as p WHERE o.name LIKE
>> CONCAT('%', p.name, '%');
>> will retrieve exact matches only.
>>
>> Any advice will be appreciated.
>
> Hi,
>
> I never saw LIKE used in that way ever before, and am unsure if that is
> possible.
> I only used/saw LIKE with a literal string, not a dynamic construct, like
> yours, joined on another table.
> I would advise you to dive into the documentation for your version of
> mysql,
> to first check if it is allowed anyway.
>
> If it is not allowed: solve the problem programmatically. Get the table
> into
> your scriptingenvironment and solve the join in there.
>
> But once again: I could be completely wrong, and maybe it IS possible in
> mysql.
>
> Regards,
> Erwin Moller
Hiya,
Thanks for all replies.
The following solution posted by Jake works like a charm.
"Jake Krohn" <krohnk@ece.cmu.edu> wrote in message
news:dvehtp$5se$1@nntp.ece.cmu.edu...
> Try this:
> SELECT * FROM table1 a, table2 b
> WHERE INSTR(a.name, b.name) <> 0
> Jake Krohn
The doc from mysql.com has this to say:
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in string
str. This is the same as the two-argument form of LOCATE(), except that the
order of the arguments is reversed.
mysql> SELECT INSTR('foobarbar', 'bar');
-> 4
mysql> SELECT INSTR('xbar', 'foobar');
-> 0
This function is multi-byte safe. In MySQL 3.23, this function is case
sensitive. For 4.0 on, it is case sensitive only if either argument is a
binary string.
cheers.
[Back to original message]
|