Retrieve fields with similar values from 2 tables?

[no.mail.pls]

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.

PostScript: i posted the above in mysql groups, but problem remains, so am
trying my luck here.

TIA

[Erwin Moller]

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 programmaticall y. Get the table into
your scriptingenviro nment and solve the join in there.

But once again: I could be completely wrong, and maybe it IS possible in
mysql.

Regards,
Erwin Moller

PostScript: i posted the above in mysql groups, but problem remains, so am
trying my luck here.

TIA

[Jerry Stuckle]

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.

PostScript: i posted the above in mysql groups, but problem remains, so am
trying my luck here.

TIA

You've also asked this in (at least) comp.databases. mysql and
alt.comp.databa ses.mysql (where is is much more appropriate).

And please learn how to cross-post.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[noone]

Erwin Moller wrote:

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 programmaticall y. Get the table into
your scriptingenviro nment and solve the join in there. But once again: I could be completely wrong, and maybe it IS possible in
mysql. Regards,
Erwin Moller

PostScript: i posted the above in mysql groups, but problem remains, so am
trying my luck here.

TIA

I would probably do something like in PHP:

select name from table2;

in a while loop

[noone]

Erwin Moller wrote:

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 programmaticall y. Get the table into
your scriptingenviro nment and solve the join in there. But once again: I could be completely wrong, and maybe it IS possible in
mysql. Regards,
Erwin Moller

PostScript: i posted the above in mysql groups, but problem remains, so am
trying my luck here.

TIA

I would probably do something like in PHP:

psuedo-code:
select name from table2;
while values
select * from table1 where name like '%$table2name%'
do more stuff... with second result set...

[Gordon Burditt]

>>> 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.
Unless you have a name that contains the string 'p.name', literally,
you won't.
(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 don't see why not.
I only used/saw LIKE with a literal string, not a dynamic construct, like
yours, joined on another table.
Granted, it may not be done that way very often, but it's possible.
I would advise you to dive into the documentation for your version of mysql,
to first check if it is allowed anyway.

I have used:
... WHERE '$email' like whitelist.patte rn;

on occasion in email filtering applications. pattern might contain
something like '%@mycustomer.c om' or '%@%.mycompany. com'. It's not
real efficient (MySQL probably has to do a patternmatch on every
row, especially if the leading character in the pattern is a %, as
it often is. But it works. And it may be much easier to use
patterns than to use lots more fixed strings and separate tables
for host, user@host, user, etc.

Also possible is:

... WHERE normalize('$ema il') like normalize(white list.pattern);

where the normalize() function converts to lower case, removes leading
and trailing spaces, removes comments, etc. This one is also likely
difficult to optimize. normalize() would either be implemented with
stored procs or be expanded inline with calls to a bunch of other
SQL functions.

Gordon L. Burditt

[no.mail.pls]

"Erwin Moller"
<si************ *************** *************** @spamyourself.c om> wrote in
message news:44******** *************** @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 programmaticall y. Get the table
into
your scriptingenviro nment 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" <kr****@ece.cmu .edu> wrote in message
news:dv******** **@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,subst r)

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('foobarba r', '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.

[no.mail.pls]

"Jerry Stuckle" <js*******@attg lobal.net> wrote in message
news:FO******** ************@co mcast.com...

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.

PostScript: i posted the above in mysql groups, but problem remains, so
am trying my luck here.

TIA

You've also asked this in (at least) comp.databases. mysql and
alt.comp.databa ses.mysql (where is is much more appropriate).

And please learn how to cross-post.

Pethaps to x-post or not is a choice between the devil and the deep blue
sea, even though not all of us believe in devils, and some of us love the
deep blue sea.

Tiptoeing gently among the mines in Usenet can be a tricky bizness.

cheers

[Jerry Stuckle]

no.mail.pls wrote:

"Jerry Stuckle" <js*******@attg lobal.net> wrote in message
news:FO******** ************@co mcast.com...

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.

PostScript : i posted the above in mysql groups, but problem remains, so
am trying my luck here.

TIA

You've also asked this in (at least) comp.databases. mysql and
alt.comp.data bases.mysql (where is is much more appropriate).

And please learn how to cross-post.

Pethaps to x-post or not is a choice between the devil and the deep blue
sea, even though not all of us believe in devils, and some of us love the
deep blue sea.

Tiptoeing gently among the mines in Usenet can be a tricky bizness.

cheers

And people who want answers should follow usenet conventions - which
includes cross-posting instead of multi-posting.
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[no.mail.pls]

"Jerry Stuckle" <js*******@attg lobal.net> wrote in message
news:Qd******** *************** *******@comcast .com...

no.mail.pls wrote:

"Jerry Stuckle" <js*******@attg lobal.net> wrote in message
news:FO******** ************@co mcast.com...

no.mail.pl s 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.

PostScrip t: i posted the above in mysql groups, but problem remains, so
am trying my luck here.

TIA

You've also asked this in (at least) comp.databases. mysql and
alt.comp.dat abases.mysql (where is is much more appropriate).

And please learn how to cross-post.

Pethaps to x-post or not is a choice between the devil and the deep blue
sea, even though not all of us believe in devils, and some of us love the
deep blue sea.

Tiptoeing gently among the mines in Usenet can be a tricky bizness.

cheers

And people who want answers should follow usenet conventions - which
includes cross-posting instead of multi-posting.

Perhaps it is more accurate to say that it includes, as well as excludes,
x-posting. Sell not the devil short.

cheers