> 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%';
Out of curiousity, does the order matter? I have a JOIN with about 6 tables, some are very small, some are quite large. I know with LEFT JOIN if I switched the order of the tables around, the queries could speed up or slow down dramatically. Is the same true with INNER JOIN? Still trying to completely grasp JOINs, getting closer though... Also, shouldn't the second INNER JOIN have an "ON" keyword? Josh > 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. --------------------------------------------------------------------- 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
