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
13  2166 
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
PostScript: i posted the above in mysql groups, but problem remains, so am
trying my luck here.
TIA
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.databases.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*******@attglobal.net
==================
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 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
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
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 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
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...
>>> 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.pattern;
on occasion in email filtering applications. pattern might contain
something like '%@mycustomer.com' 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('$email') like normalize(whitelist.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
"Erwin Moller"
<si******************************************@spam yourself.com> 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 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" <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,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.
"Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:FO********************@comcast.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.databases.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
no.mail.pls wrote: "Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:FO********************@comcast.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.databases.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*******@attglobal.net
==================
"Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:Qd******************************@comcast.com. .. no.mail.pls wrote: "Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:FO********************@comcast.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.databases.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
no.mail.pls wrote: "Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:Qd******************************@comcast.com. ..
no.mail.pls wrote:
"Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:FO********************@comcast.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.databases.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
Only in your book does it include multi-posting to multiple newsgroups.
If you want the help, follow the conventions. Otherwise soon you won't
be able to get any responses to any of your questions.
Remember - we're all volunteers here. None of us are being paid to help
you. And we don't have to.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp. js*******@attglobal.net
==================
"Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:E5********************@comcast.com... no.mail.pls wrote: "Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:Qd******************************@comcast.com. ..
no.mail.pls wrote:
"Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:FO********************@comcast.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.databases.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
Only in your book does it include multi-posting to multiple newsgroups.
If you want the help, follow the conventions. Otherwise soon you won't be
able to get any responses to any of your questions.
Remember - we're all volunteers here. None of us are being paid to help
you. And we don't have to.
Appreciate if you can show me the convention that multi-posting is not
allowed, bearing in mind that i first asked in one newsgroup, and failing to
get a solution, i asked in another.
If what you claimed as convention is true, then i should never ask the same
question again in another newsgroup, even though there is no solution from
the first group i posted in.
And if x-posting is allowed, then i should post a question in all relevant
newsgroups simultaneously.
Is the above true?
cheers
no.mail.pls wrote: "Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:E5********************@comcast.com... no.mail.pls wrote: "Jerry Stuckle" <js*******@attglobal.net> wrote in message
news:Qd******************************@comcast.com. ..
no.mail.pls wrote:
>"Jerry Stuckle" <js*******@attglobal.net> wrote in message
>news:FO********************@comcast.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.databases.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
Only in your book does it include multi-posting to multiple newsgroups.
If you want the help, follow the conventions. Otherwise soon you won't
be able to get any responses to any of your questions.
Remember - we're all volunteers here. None of us are being paid to help
you. And we don't have to.
Appreciate if you can show me the convention that multi-posting is not
allowed, bearing in mind that i first asked in one newsgroup, and failing
to get a solution, i asked in another.
If what you claimed as convention is true, then i should never ask the
same question again in another newsgroup, even though there is no solution
from the first group i posted in.
And if x-posting is allowed, then i should post a question in all relevant
newsgroups simultaneously.
Is the above true?
cheers
Hi,
Allow me to drop in here.
Jerry was only warning you that multiposting can work against you.
If I see the same question in more newsgroups I follow, I almost never
answer because I don't know where to answer, and am unsure if the poster is
checking them all. Even if I perfectly well know how to help.
So you can ask for conventions and such, but that won't help. You can find
1000 opinionson how to behave on usenet.
Bottomline is that many do not appreciate multipost, and in the end it will
work against you.
(Just ment as a kind word of advise)
Best of luck,
Erwin Moller
no.mail.pls wrote:
Appreciate if you can show me the convention that multi-posting is not
allowed, bearing in mind that i first asked in one newsgroup, and failing to
get a solution, i asked in another.
If what you claimed as convention is true, then i should never ask the same
question again in another newsgroup, even though there is no solution from
the first group i posted in.
And if x-posting is allowed, then i should post a question in all relevant
newsgroups simultaneously.
Is the above true?
cheers
You should pick and choose the most likely group(s) where your question could be
asked. Then cross-post to those groups. Typically you should post to at most
three or four groups; if you need to post to more generally your question is too
broad (you haven't identified the problem yet) or you're posting to
inappropriate groups.
Once you have posted - wait at least 24 to 48 hours for a response. If you
haven't gotten a response by that time, chances are your question was not clear
or was not appropriate to those newsgroups. Seldom have I ever seen a message
go longer than that (at least in busy newsgroups) not be responded to in that time.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp. js*******@attglobal.net
================== This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: AAVF |
last post by:
Hi
We have a problem with a query.
An Access database links via ODBC to a UNIX server. To speed things, we use
the ODBC to load the relevant tables to the local PC that runs Access so
that...
|
by: Megan |
last post by:
Hi everybody-
I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the...
|
by: google |
last post by:
I have a database with four table. In one of the tables, I use about
five lookup fields to get populate their dropdown list. I have read
that lookup fields are really bad and may cause problems...
|
by: jay |
last post by:
I am using the dataset object to add a row to a sql server database in vb.net
code, as follows:
dim drow as DataRow
dim cmdBld as new SqlCommandBuilder(mySqlDataAdapter)
ds.tables(0).NewRow()...
|
by: fstenoughsnoopy |
last post by:
Ok the complete story. I have a Contact Table, Query and Form, that
are used to input
and store the contact info for customers. They have FirstName, LastName
and Address as the primary key...
|
by: SueA |
last post by:
Can anyone help me with the code to retrieve the name of the table that
a control on a form (that's built off a query) refers to?
For instance:
qryMfrsAndBrands is based on the tables:...
|
by: kev |
last post by:
Hi all,
I have created a database for equipments. I have a form to register the
equipment meaning filling in all the particulars (ID, serial, type,
location etc). I have two buttons at the end...
|
by: maxamis4 |
last post by:
Hello folks,
Here is the backgroup. I am creating an agent that can find a user in LDAP and return the last logon date. Now i am not sure if with active directory you can user the...
|
by: Evanescent |
last post by:
Hi Guys, I am trying to create a form which allows the users to retrieve records based on the values entered or chosen in the various combo boxes and textboxes, such as the customer's name, invoice...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
