Oops! Quite right -- ON is necessary after the word JOIN. That was written in the email compiler :-) Sorry I should have proofread it before hitting Send.
Arthur ----- Original Message ----- From: "Josh Trutwin" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, September 24, 2002 12:46 PM Subject: RE: Basic SQL join question > > 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 > --------------------------------------------------------------------- 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
