SELECT * FROM Projects INNER JOIN KeywordLink ON Projects.Id = KeywordLink.Pid INNER JOIN Keywords KeywordLink.Kid = Keywords.Id WHERE Keyword LIKE '%historical%' AND Keyword like '%scenic%';
1. Note the single quotes. 2. You can place the join logic in the WHERE clause but I prefer the clarity obtained by keeping it in JOIN clauses and using the WHERE only to contain the "include if" logic. hth, Arthur -----Original Message----- From: Adam Randall [mailto:[EMAIL PROTECTED]] Sent: Saturday, September 21, 2002 5:18 AM To: [EMAIL PROTECTED] Subject: Basic SQL join question Okay, I've thought about this every way I can conceive of, but I cannot figure out the sql query logic involved in joining three tables together. Here is what I am trying to do: I have three tables: keywords keywordlink projects keywords has these fields: id keyword keywordlink has these fields: id pid kid projects has a lot of fields, but it's primary key is ID What keywords holds is the keywords used in the various different tables in the database. keywordlink associates a project with several keywords: example keywords: id | keyword 1 | landscape 2 | historical 3 | scenic example keywordlink: id | pid | kid 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 example projects: id | name 1 | example 2 | extra Now, what I am trying to do is basically search the keywords database for keyword names, and then return a list of project names that are associated with those keywords. If the keywords were stored in the projects database, this is basically what I would want it to do (assume all the keywords are stored in a field called "keywords" in the projects table): SELECT * FROM projects where keywords like "%historical%" and keywords like "%scenic%"; This would return to me the projects that have historical and scenic in the keywords field. Now, how do I do this same operation with it broken out like I have above. The reason I am not storing the keywords in the projects table is that it would be quite a chore in changing the keywords in the system if I did that (modify one keyword, modify all the projects, etc). Anyone have any words of advice for me? Adam. -- ----------------------------------------------------------------------- Adam Randall http://www.xaren.net/ [EMAIL PROTECTED] http://nt.xaren.net/ [EMAIL PROTECTED] "Macintosh users are a special case. They care passionately about the Mac OS and would rewire their own bodies to run on Mac OS X if such a thing were possible." -- Peter H. Lewis --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
