Oracle SQL Query - Element containing every element in subquery

I have 3 tables like so :

Document(ID:integer, Title:string)  Keywords(ID:integer, Name:string)  Document_Keywords(DocumentID:integer, KeywordID:integer)    Document_Keywords.DocumentID referencing Document.ID  Document_Keywords.KeywordID referencing Keywords.ID  

A document contains [0, n] keywords.

I want to get every Document which Keywords contains at least a set of another Document's Keywords. As so:

Foo, Bar and Fred-> Documents  Foo's keywords: {1, 2, 3}  Bar's keywords: {1, 2, 3, 4}  Fred's keywords: {1, 3, 5}  

If we search for all the documents keywords containing Foo's keywords, we get Bar but not Fred.

Here is the query I have so far:

SELECT KeywordID  FROM Document_Keywords DK  JOIN Document D ON D.ID = DK.DocumentID  WHERE D.title = 'Foo'  MINUS  SELECT KeywordID  FROM Document_Keywords  WHERE DocumentID = 1;  

It returns an empty table if the Document with ID = 1 keywords contains at least every keywords of Foo's.

I can't find any other ways to solve this probleme as I can only use Oracle SQL to answer it.

https://stackoverflow.com/questions/67236280/oracle-sql-query-element-containing-every-element-in-subquery April 24, 2021 at 04:08AM