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:randalla@stripped]
> > Sent: Saturday, September 21, 2002 5:18 AM
> > To: mysql@stripped
> > 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
john@stripped
501-228-0317
http://www.CoreReader.com/
