I'd just like to say thanks to both Michael and Rocar for both solutions. I will be giving them a go.
I now need to buy a more comprehensive manual for MySQL, as neither of these commands are in the 2 books I currently have. Many thanks Ian -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 11:29 PM To: Ian Izzard Cc: Rocar Peças; [EMAIL PROTECTED] Subject: 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 <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 > ________________________________________________________________________ > > 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]
