SELECT from multiple criteria

Question

I have a database with a documents table

DocumentID   DocTitle   DocDesc

and a keyword table

KeywordID    Keyword

linked by a DocumentKeyword table

DocumentID   KeywordID

I have used a view to give a dataset of all the document-keyword realtionships.

DocumentID   DocTitle   DocDesc   Keyword

I am looking for a select statement that will let me search for documents by keyword. For one keyword this is no problem I can do it. But I would like to be able to use more than one keyword for the query, and return the documentID (once) for documents that are tagged with all the given keywords.

I am stuck, my biggest problem is that I can't even work out what to search for. I realise that the view may not be necessary.

Any help would be greatly appreciated.

Thanks

Answer 1

If you need to specify the keyword, and not the keywordid value - use:

  SELECT d.documentid
    FROM DOCUMENT d
    JOIN DOCUMENTKEYWORD dk ON dk.documentid = d.documentid
    JOIN KEYWORD k ON k.keywordid = dk.keywordid
                  AND k.keyword IN ('keyword1', 'keyword2', 'keyword3')
GROUP BY d.documentid
  HAVING COUNT(DISTINCT k.keyword) = 3

The key thing is the combination of the IN and HAVING clauses - the COUNT must match the number of values supplied in the IN clause.

If it were keywordid, you could save a join and use:

  SELECT d.documentid
    FROM DOCUMENT d
    JOIN DOCUMENTKEYWORD dk ON dk.documentid = d.documentid
                           AND dk.keywordid IN (1, 2, 3)
GROUP BY d.documentid
  HAVING COUNT(DISTINCT dk.keywordid) = 3