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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
