if you want a way to quickly experiment with sql joins, try corereader. it's a free download from http://www.corereader.com/
after you make a data connection, you press the load button to load the metadata. after that, everything is point and click to select from drop-down lists. that makes it super quick and easy to experiment with joins until you get a feel for them. when you find the query that you want, you can open the sql frame to see the sql statement that it built. you can also save the statements into a library for future use. the negative is that it runs only under ms windows. the positive is that it will query any data source on any platform from mainframes to spreadsheets, including mysql. ( ok, so i'm proud of my work. :) ) > > 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. > > > -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ --------------------------------------------------------------------- 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
