Newbie question on Comparing fields in 2 tables?

------_=_NextPart_001_01C406C1.E03B8636
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,
=20
I=20am=20new=20to=20using=20MySQL=20and=20SQL=20queries.=20= 20I=20have=20i=
n=20the=20past=20only=20carried=20out=20simple=20queries.=20 =20I=20am=20tr=
ying=20to=20write=20a=20query=20for=20use=20in=20our=20Helpd esk/Audit=20so=
ftware.
=20
I=20want=20to=20compare=20the=20values=20in=20one=20table=20 (Keywords)=20t=
o=20the=20values=20found=20in=20another=20table=20(Software) =20so=20that=20=
I=20can=20get=20records=20of=20games=20that=20are=20installe d=20on=20PCs.=20=
=20The=20Software=20table=20is=20created=20from=20an=20audit =20run=20on=20=
each=20PC. The=20keywords=20table=20is=20created=20by=2 0myself.
=20
The=20keywords=20table=20contains=202=20fields,=20ID=20and=2 0Searchname.=20=
=20A=20sample=20of=20the=20data=20in=20this=20table=20would= 20be:
=20
ID =20Searchname
1 worm
2 kazaa
3 delta
4 game
=20
The=20software=20table=20has=202=20fields,=20pcname=20and=20 product. =
A=20sample=20of=20the=20data=20in=20this=20table=20would=20b e:
=20
pcname =20product
SW0638 CADS=20Support
SW0638 Citrix=20ICA=20Client
SW0638 Winzip
SW0653 Winzip
SW0653 Delta=20Force=202
SW0462 Winzip
SW0462 Delta=20Force
SW0462 Worms=202000
SW0785 Winzip
SW0785 Worms2
=20
The=20software=20table=20has=20some=2050,000=20records=20in= 20it. Wha=
t=20I=20am=20looking=20to=20do=20is=20to=20pick=20out=20the= 20pcname=20fro=
m=20the=20software=20table,=20where=20the=20product=20field= 20contains=20t=
he=20searchname=20from=20the=20keywords=20table. Someth ing=20like:
=20
select=20pcname,=20product=20from=20software,=20keywords=20w here=20product=
=20like=20searchname
=20
I=20would=20then=20expect=20the=20results=20to=20come=20out= 20as:
=20
pcname =20product
SW0653 Delta=20Force=202
SW0462 Delta=20Force
SW0462 Worms=202000
SW0785 Worm2
=20
=20
I=20have=20tried=20using=20the=20LIKE=20command,=20but=20the =20manuals=20o=
nly=20show=20examples=20when=20comparing=20a=20field=20to=20 a=20string,=20=
ie=20product=20LIKE=20'worm%'
=20
As=20the=20keyword=20table=20is=20likely=20to=20get=20quite= 20long=20(curr=
ently=20163=20records)=20I=20don't=20want=20to=20do=20a=20qu ery=20using=20=
the=20LIKE=20command=20immediately=20above=20(,=20as=20the=2 0query=20will=20=
get=20very=20long=20and=20unmanageable.
=20
Can=20someone=20help=20with=20a=20solution=20to=20this?=20=2 0Can=20it=20be=
=20done=20in=20a=20single=20query? Is=20there=20a=20com mand=20that=20=
I=20just=20haven't=20yet=20found?
=20
If=20it=20is=20of=20any=20help,=20the=20version=20of=20MySQL =20being=20use=
d=204.0.16. This=20version=20is=20installed=20by=20the= 20Helpdesk/Aud=
it=20software,=20and=20so=20cannot=20be=20upgraded.
=20
Your=20help=20is=20greatly=20appreciated.
=20
Ian=20Izzard

Visit=20our=20web=20site=20at=20www.scottwilson.com

Privilege=20and=20Confidentiality=20Notice.
This=20e-mail=20and=20any=20attachments=20to=20it=20are=20in tended=20only=20=
for=20the=20party
to=20whom=20they=20are=20addressed. They=20may=20contai n=20privileged=
=20and/or
confidential=20information. If=20you=20have=20received= 20this=20trans=
mission=20in=20
error,=20please=20notify=20the=20sender=20immediately=20and= 20delete=20any=
=20digital
copies=20and=20destroy=20any=20paper=20copies.
Thank=20you.


Scott=20Wilson=20Kirkpatrick=20&=20Co=20Ltd
Registered=20in=20London:=20No.=20880328
Registered=20office:=20Scott=20House,=20Basing=20View,
Basingstoke,=20Hampshire,=20RG21=204JG.=20UK.
____________________________________________________________ ____________
This=20e-mail=20has=20been=20scanned=20for=20all=20viruses=2 0by=20Star=20I=
nternet.=20The
service=20is=20powered=20by=20MessageLabs.=20For=20more=20in formation=20on=
=20a=20proactive
anti-virus=20service=20working=20around=20the=20clock,=20aro und=20the=20gl=
obe,=20visit:
http://www.star.net.uk
____________________________________________________________ ____________
------_=_NextPart_001_01C406C1.E03B8636--

Re: Newbie question on Comparing fields in 2 tables?

Mr. Izzard,

We have these tables:

Table software
- pcname char(..)
- product char(..)

Table keywords
- id int(..)
- seachname char(...)

and you want to pick out the pcname from the software table, where the
product field contains the searchname from the keywords table

Try this and you´ll succeed:
==================================================
SELECT
software.pcname,
software.product

FROM
software, keywords

WHERE
software.product LIKE CONCAT("%", keywords.searchname, "%")
==================================================

Best wishes,

Leandro M Neves,
ROCAR PEÇAS LTD.
Sete Lagoas/MG - Brazil

----- Original Message -----
From: "Ian Izzard"
To:
Sent: Wednesday, March 10, 2004 2:05 PM
Subject: Newbie question on Comparing fields in 2 tables?


Hi,

I am new to using MySQL and SQL queries. I have in the past only carried
out simple queries. I am trying to write a query for use in our
Helpdesk/Audit software.

I want to compare the values in one table (Keywords) to the values found in
another table (Software) so that I can get records of games that are
installed on PCs. The Software table is created from an audit run on each
PC. The keywords table is created by myself.

The keywords table contains 2 fields, ID and Searchname. A sample of the
data in this table would be:

ID Searchname
1 worm
2 kazaa
3 delta
4 game

The software table has 2 fields, pcname and product. A sample of the data
in this table would be:

pcname product
SW0638 CADS Support
SW0638 Citrix ICA Client
SW0638 Winzip
SW0653 Winzip
SW0653 Delta Force 2
SW0462 Winzip
SW0462 Delta Force
SW0462 Worms 2000
SW0785 Winzip
SW0785 Worms2

The software table has some 50,000 records in it. What I am looking to do
is to pick out the pcname from the software table, where the product field
contains the searchname from the keywords table. Something like:

select pcname, product from software, keywords where product like searchname

I would then expect the results to come out as:

pcname product
SW0653 Delta Force 2
SW0462 Delta Force
SW0462 Worms 2000
SW0785 Worm2


I have tried using the LIKE command, but the manuals only show examples when
comparing a field to a string, ie product LIKE 'worm%'

As the keyword table is likely to get quite long (currently 163 records) I
don't want to do a query using the LIKE command immediately above (, as the
query will get very long and unmanageable.

Can someone help with a solution to this? Can it be done in a single query?
Is there a command that I just haven't yet found?

If it is of any help, the version of MySQL being used 4.0.16. This version
is installed by the Helpdesk/Audit software, and so cannot be upgraded.

Your help is greatly appreciated.

Ian Izzard

Visit our web site at www.scottwilson.com

Privilege and Confidentiality Notice.
This e-mail and any attachments to it are intended only for the party
to whom they are addressed. They may contain privileged and/or
confidential information. If you have received this transmission in
error, please notify the sender immediately and delete any digital
copies and destroy any paper copies.
Thank you.


Scott Wilson Kirkpatrick & Co Ltd
Registered in London: No. 880328
Registered office: Scott House, Basing View,
Basingstoke, Hampshire, RG21 4JG. UK.
____________________________________________________________ ____________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
____________________________________________________________ ____________


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Newbie question on Comparing fields in 2 tables?

An equivalent, but slightly simpler, query would be

SELECT sw.pcname, sw.product
FROM software sw, keywords kw
WHERE sw.product RLIKE kw.Searchname

RLIKE is for regular expression pattern matching. Regular expressions,
unlike LIKE patterns, don't have to match the whole string, so there is
no need to paste '%' onto each end.

For more, see




Michael

Rocar Peças wrote:

> Mr. Izzard,
>
> We have these tables:
>
> Table software
> - pcname char(..)
> - product char(..)
>
> Table keywords
> - id int(..)
> - searchname char(...)
>
> and you want to pick out the pcname from the software table, where the
> product field contains the searchname from the keywords table
>
> Try this and you´ll succeed:
> ==================================================
> SELECT
> software.pcname,
> software.product
>
> FROM
> software, keywords
>
> WHERE
> software.product LIKE CONCAT("%", keywords.searchname, "%")
> ==================================================
>
> Best wishes,
>
> Leandro M Neves,
> ROCAR PEÇAS LTD.
> Sete Lagoas/MG - Brazil
>
> ----- Original Message -----
> From: "Ian Izzard"
> To:
> Sent: Wednesday, March 10, 2004 2:05 PM
> Subject: Newbie question on Comparing fields in 2 tables?
>
>
> Hi,
>
> I am new to using MySQL and SQL queries. I have in the past only carried
> out simple queries. I am trying to write a query for use in our
> Helpdesk/Audit software.
>
> I want to compare the values in one table (Keywords) to the values found in
> another table (Software) so that I can get records of games that are
> installed on PCs. The Software table is created from an audit run on each
> PC. The keywords table is created by myself.
>
> The keywords table contains 2 fields, ID and Searchname. A sample of the
> data in this table would be:
>
> ID Searchname
> 1 worm
> 2 kazaa
> 3 delta
> 4 game
>
> The software table has 2 fields, pcname and product. A sample of the data
> in this table would be:
>
> pcname product
> SW0638 CADS Support
> SW0638 Citrix ICA Client
> SW0638 Winzip
> SW0653 Winzip
> SW0653 Delta Force 2
> SW0462 Winzip
> SW0462 Delta Force
> SW0462 Worms 2000
> SW0785 Winzip
> SW0785 Worms2
>
> The software table has some 50,000 records in it. What I am looking to do
> is to pick out the pcname from the software table, where the product field
> contains the searchname from the keywords table. Something like:
>
> select pcname, product from software, keywords where product like searchname
>
> I would then expect the results to come out as:
>
> pcname product
> SW0653 Delta Force 2
> SW0462 Delta Force
> SW0462 Worms 2000
> SW0785 Worm2
>
>
> I have tried using the LIKE command, but the manuals only show examples when
> comparing a field to a string, ie product LIKE 'worm%'
>
> As the keyword table is likely to get quite long (currently 163 records) I
> don't want to do a query using the LIKE command immediately above (, as the
> query will get very long and unmanageable.
>
> Can someone help with a solution to this? Can it be done in a single query?
> Is there a command that I just haven't yet found?
>
> If it is of any help, the version of MySQL being used 4.0.16. This version
> is installed by the Helpdesk/Audit software, and so cannot be upgraded.
>
> Your help is greatly appreciated.
>
> Ian Izzard
>
> Visit our web site at www.scottwilson.com
>
> Privilege and Confidentiality Notice.
> This e-mail and any attachments to it are intended only for the party
> to whom they are addressed. They may contain privileged and/or
> confidential information. If you have received this transmission in
> error, please notify the sender immediately and delete any digital
> copies and destroy any paper copies.
> Thank you.
>
>
> Scott Wilson Kirkpatrick & Co Ltd
> Registered in London: No. 880328
> Registered office: Scott House, Basing View,
> Basingstoke, Hampshire, RG21 4JG. UK.
> ____________________________________________________________ ____________
> This e-mail has been scanned for all viruses by Star Internet. The
> service is powered by MessageLabs. For more information on a proactive
> anti-virus service working around the clock, around the globe, visit:
> http://www.star.net.uk
> ____________________________________________________________ ____________
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

RE: Newbie question on Comparing fields in 2 tables?

I'd=20just=20like=20to=20say=20thanks=20to=20both=20Michael= 20and=20Rocar=20=
for=20both=20solutions. I=20will=20be=20giving=20them=2 0a=20go.

I=20now=20need=20to=20buy=20a=20more=20comprehensive=20manua l=20for=20MySQ=
L,=20as=20neither=20of=20these=20commands=20are=20in=20the=2 02=20books=20I=
=20currently=20have.


Many=20thanks

Ian


-----Original=20Message-----
From:=20Michael=20Stassen=20[mailto:Michael.Stassen@verizon. net]
Sent:=20Wednesday,=20March=2010,=202004=2011:29=20PM
To:=20Ian=20Izzard
Cc:=20Rocar=20Pe=E7as;=20mysql@lists.mysql.com
Subject:=20Re:=20Newbie=20question=20on=20Comparing=20fields =20in=202=20ta=
bles?


An=20equivalent,=20but=20slightly=20simpler,=20query=20would =20be

=20SELECT=20sw.pcname,=20sw.product
=20FROM=20software=20sw,=20keywords=20kw
=20WHERE=20sw.product=20RLIKE=20kw.Searchname

RLIKE=20is=20for=20regular=20expression=20pattern=20matching . Regular=
=20expressions,=20
unlike=20LIKE=20patterns,=20don't=20have=20to=20match=20the= 20whole=20stri=
ng,=20so=20there=20is=20
no=20need=20to=20paste=20'%'=20onto=20each=20end.

For=20more,=20see

=20
=20

Michael

Rocar=20Pe=E7as=20wrote:

>=20Mr.=20Izzard,
>=20
>=20We=20have=20these=20tables:
>=20
>=20Table =20software
> =20=2 0=20-=20pcname=
=20char(..)
> =20=2 0=20-=20produc=
t=20char(..)
>=20
>=20Table =20keywords
> =20=2 0=20-=20id=20i=
nt(..)
> =20=2 0=20-=20search=
name=20char(...)
>=20
>=20and=20you=20want=20to=20pick=20out=20the=20pcname=20from =20the=20softw=
are=20table,=20where=20the
>=20product=20field=20contains=20the=20searchname=20from=20t he=20keywords=20=
table
>=20
>=20Try=20this=20and=20you=B4ll=20succeed:
> ==================3D=3 D=====3D=
==================== =====3D=
=3D
>=20SELECT
> =20software.pcname,
> =20software.product
>=20
>=20FROM
> =20software,=20keywords
>=20
>=20WHERE
> =20software.product=20LIKE=20CONCAT("%",=20keyw ords.searchnam=
e,=20"%")
> ==================3D=3 D=====3D=
==================== =====3D=
=3D
>=20
>=20Best=20wishes,
>=20
>=20Leandro=20M=20Neves,
>=20ROCAR=20PE=C7AS=20LTD.
>=20Sete=20Lagoas/MG=20-=20Brazil
>=20
>=20-----=20Original=20Message=20-----=20
>=20From:=20"Ian=20Izzard"=20
>=20To:=20
>=20Sent:=20Wednesday,=20March=2010,=202004=202:05=20PM
>=20Subject:=20Newbie=20question=20on=20Comparing=20fields=2 0in=202=20tabl=
es?
>=20
>=20
>=20Hi,
>=20
>=20I=20am=20new=20to=20using=20MySQL=20and=20SQL=20queries. I=20have=
=20in=20the=20past=20only=20carried
>=20out=20simple=20queries. I=20am=20trying=20to=20writ e=20a=20query=20=
for=20use=20in=20our
>=20Helpdesk/Audit=20software.
>=20
>=20I=20want=20to=20compare=20the=20values=20in=20one=20tabl e=20(Keywords)=
=20to=20the=20values=20found=20in
>=20another=20table=20(Software)=20so=20that=20I=20can=20get =20records=20o=
f=20games=20that=20are
>=20installed=20on=20PCs. The=20Software=20table=20is=2 0created=20fro=
m=20an=20audit=20run=20on=20each
>=20PC. The=20keywords=20table=20is=20created=20by=20my self.
>=20
>=20The=20keywords=20table=20contains=202=20fields,=20ID=20a nd=20Searchnam=
e. A=20sample=20of=20the
>=20data=20in=20this=20table=20would=20be:
>=20
>=20ID =20Searchname
>=201 worm
>=202 kazaa
>=203 delta
>=204 game
>=20
>=20The=20software=20table=20has=202=20fields,=20pcname=20an d=20product.=20=
=20A=20sample=20of=20the=20data
>=20in=20this=20table=20would=20be:
>=20
>=20pcname =20product
>=20SW0638 CADS=20Support
>=20SW0638 Citrix=20ICA=20Client
>=20SW0638 Winzip
>=20SW0653 Winzip
>=20SW0653 Delta=20Force=202
>=20SW0462 Winzip
>=20SW0462 Delta=20Force
>=20SW0462 Worms=202000
>=20SW0785 Winzip
>=20SW0785 Worms2
>=20
>=20The=20software=20table=20has=20some=2050,000=20records=2 0in=20it. =
What=20I=20am=20looking=20to=20do
>=20is=20to=20pick=20out=20the=20pcname=20from=20the=20softw are=20table,=20=
where=20the=20product=20field
>=20contains=20the=20searchname=20from=20the=20keywords=20ta ble. Some=
thing=20like:
>=20
>=20select=20pcname,=20product=20from=20software,=20keywords =20where=20pro=
duct=20like=20searchname
>=20
>=20I=20would=20then=20expect=20the=20results=20to=20come=20 out=20as:
>=20
>=20pcname =20product
>=20SW0653 Delta=20Force=202
>=20SW0462 Delta=20Force
>=20SW0462 Worms=202000
>=20SW0785 Worm2
>=20
>=20
>=20I=20have=20tried=20using=20the=20LIKE=20command,=20but=2 0the=20manuals=
=20only=20show=20examples=20when
>=20comparing=20a=20field=20to=20a=20string,=20ie=20product= 20LIKE=20'worm=
%'
>=20
>=20As=20the=20keyword=20table=20is=20likely=20to=20get=20qu ite=20long=20(=
currently=20163=20records)=20I
>=20don't=20want=20to=20do=20a=20query=20using=20the=20LIKE= 20command=20im=
mediately=20above=20(,=20as=20the
>=20query=20will=20get=20very=20long=20and=20unmanageable.
>=20
>=20Can=20someone=20help=20with=20a=20solution=20to=20this?= 20=20Can=20it=20=
be=20done=20in=20a=20single=20query?
>=20Is=20there=20a=20command=20that=20I=20just=20haven't=20y et=20found?
>=20
>=20If=20it=20is=20of=20any=20help,=20the=20version=20of=20M ySQL=20being=20=
used=204.0.16. This=20version
>=20is=20installed=20by=20the=20Helpdesk/Audit=20software,=2 0and=20so=20ca=
nnot=20be=20upgraded.
>=20
>=20Your=20help=20is=20greatly=20appreciated.
>=20
>=20Ian=20Izzard
>=20
>=20Visit=20our=20web=20site=20at=20www.scottwilson.com
>=20
>=20Privilege=20and=20Confidentiality=20Notice.
>=20This=20e-mail=20and=20any=20attachments=20to=20it=20are= 20intended=20o=
nly=20for=20the=20party
>=20to=20whom=20they=20are=20addressed. They=20may=20co ntain=20privil=
eged=20and/or
>=20confidential=20information. If=20you=20have=20recei ved=20this=20t=
ransmission=20in
>=20error,=20please=20notify=20the=20sender=20immediately=20 and=20delete=20=
any=20digital
>=20copies=20and=20destroy=20any=20paper=20copies.
>=20Thank=20you.
>=20
>=20
>=20Scott=20Wilson=20Kirkpatrick=20&=20Co=20Ltd
>=20Registered=20in=20London:=20No.=20880328
>=20Registered=20office:=20Scott=20House,=20Basing=20View,
>=20Basingstoke,=20Hampshire,=20RG21=204JG.=20UK.
>=20________________________________________________________ ______________=
__
>=20This=20e-mail=20has=20been=20scanned=20for=20all=20virus es=20by=20Star=
=20Internet.=20The
>=20service=20is=20powered=20by=20MessageLabs.=20For=20more= 20information=20=
on=20a=20proactive
>=20anti-virus=20service=20working=20around=20the=20clock,=2 0around=20the=20=
globe,=20visit:
>=20http://www.star.net.uk
>=20________________________________________________________ ______________=
__
>=20
>=20


Visit=20our=20web=20site=20at=20www.scottwilson.com

Privilege=20and=20Confidentiality=20Notice.
This=20e-mail=20and=20any=20attachments=20to=20it=20are=20in tended=20only=20=
for=20the=20party
to=20whom=20they=20are=20addressed. They=20may=20contai n=20privileged=
=20and/or
confidential=20information. If=20you=20have=20received= 20this=20trans=
mission=20in=20
error,=20please=20notify=20the=20sender=20immediately=20and= 20delete=20any=
=20digital
copies=20and=20destroy=20any=20paper=20copies.
Thank=20you.


Scott=20Wilson=20Kirkpatrick=20&=20Co=20Ltd
Registered=20in=20London:=20No.=20880328
Registered=20office:=20Scott=20House,=20Basing=20View,
Basingstoke,=20Hampshire,=20RG21=204JG.=20UK.
____________________________________________________________ ____________
This=20e-mail=20has=20been=20scanned=20for=20all=20viruses=2 0by=20Star=20I=
nternet.=20The
service=20is=20powered=20by=20MessageLabs.=20For=20more=20in formation=20on=
=20a=20proactive
anti-virus=20service=20working=20around=20the=20clock,=20aro und=20the=20gl=
obe,=20visit:
http://www.star.net.uk
____________________________________________________________ ____________

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql@m.gmane.org