SELECT according to several criteria

Question

I have a database with a table of documents

DocumentID   DocTitle   DocDesc

and keyword table

KeywordID    Keyword

linked using the DocumentKeyword table

DocumentID   KeywordID

I used the view to provide a dataset of all implementations of the document keyword.

DocumentID   DocTitle   DocDesc   Keyword

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

I'm stuck, my biggest problem is that I can't even figure out what to look for. I understand that a presentation may not be necessary.

Any help would be greatly appreciated.

thank

Answer 1

keyword, keywordid, :

  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

- IN HAVING - COUNT , IN.

keywordid, :

  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

Answer 2

, , ( , , ). AND ( ORM SQL-) .

, ( ):

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;

Answer 3

, OMG Ponies, :

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