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: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.
--
-----------------------------------------------------------------------
Adam Randall http://www.xaren.net/
randalla@stripped http://nt.xaren.net/
webtech@stripped
"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 <mysql-thread120200@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-afuller=etsys.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
