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
<http://www.mysql.com/doc/en/String_comparison_functions.html> <http://www.mysql.com/doc/en/Pattern_matching.html>
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" <[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]
