Don't know how to set up this query... 1

[theweirdo]

3 tables: URLS, URL_Keywords, Keywords

URLS has the following (unique) fields: URLID, URL
URL_Keywords has the following fields: relation_ID, kID, URLID
Keywords has the following (unique) fields: kID, word

Basically Keywords stores a list of unique words and their ID, URL_Keywords stores the relationship between URLS and Keywords, and URLS stores a list of URLS

Here's an example of what the database would look like:

URLS:
URLID URL Summary
---------------------------
1 abc abcsummary
2 def defsummary

Keywords:
kID Word
---------------
1 hi
2 bye
3 hello

URL_Keywords:
relation_ID kID URLID
------------------------------
1 1 1
2 2 1
3 3 2
4 1 2


What I want to do is given a list of keyword(s), find the URL(s) that has all those keywords. For example, given keywords hi and bye, the query would return the URL "abc" but not the URL "def". Right now my query would return a URL that has EITHER of the keywords, but I only want it to return URLs that have BOTH the keywords.

Any help would be appreciated, thanks!

 

[swampBoogie]

select url from urls
 join URL_keywords
 using (urlid) join Keywords
 using (kid)
where Word in ('hi','bye')
group by url
having count(*) = 2