CODEWITHSUNDEEP
sqlsql-servert-sql
Slotty
Slotty

Reputation: 174

SELECT from multiple criteria

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

Upvotes: 3

Views: 606

Answers (3)

Slotty
Slotty

Reputation: 174

The statement as submitted by OMG Ponies but refactored to use the view:

SELECT
   d.documentid 
FROM 
   v_AllDocumentKeyword d 
WHERE 
   d.keyword IN ('Keyword1','Keyword2','Keyword3')
GROUP BY 
   d.documentid 
HAVING 
   COUNT(DISTINCT d.keyword) = 3

Thanks

Upvotes: 0

Zack Bloom
Zack Bloom

Reputation: 8427

You don't need a view to do what your doing (although your free to use one if you think it makes your code cleaner). Using AND (preferably with an ORM to do the SQL wrangling) is the easiest.

I think this would work as well (although I don't recommend using it):

SELECT DISTINCT documents.DocumentID 
    FROM documents 
    WHERE (SELECT DISTINCT count(*) FROM DocumentKeyword 
           WHERE KeywordID = ANY
               (SELECT keyword.KeywordID FROM keyword 
                WHERE Keyword 
                IN ('keyword 1', 'keyword 2', 'keyword 3'))
           AND DocumentID = documents.DocumentID)) = 3;

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332521

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

Upvotes: 5

Related Questions