I have the following table structure:

table_book_user
book_id | user_id 
-------- ---------
 b1     | u1      
 b3     | u1      
 b2     | u1      
 b4     | u2      
 b5     | u2      
 b5     | u3      

table_book_collection
book_id | collection_id 
-------- --------------
b1      | c1      
b2      | c1      
b3      | c1      
b4      | c2      
b5      | c1      
b5      | c2      
b5      | c3 

I want to find out which collection a user have. The collection is matched only if a user has all the books of the collection.

For the example above:

• u1 is missing b5 to have c1
• u2 has c2 and c3
• u3 has c3.

So the result should be:

user_id | collection_id 
-------- --------------
u2      | c2      
u2      | c3      
u3      | c3      

CodePudding user response：

You can aggregate the books into arrays then use the contains operator @> in a join condition:

with user_books as (
  select user_id, array_agg(book_id) as books
  from book_user
  group by user_id
), collections as (
  select collection_id, array_agg(book_id) as books
  from book_collection
  group by collection_id
)
select ub.user_id, col.collection_id
from user_books ub
  join collections col on ub.books @> col.books

The join condition ub.books @> col.books only returns users that have all books of that collection.

Online example

CodePudding user response：

SELECT U.user_id, C.collection_id
FROM table_book_user AS U
   LEFT JOIN table_book_collection AS C
     ON C.book_ID = U.book_id
WHERE C.collection_id NOT IN (SELECT T.collection_id FROM table_book_collection AS T
                             WHERE T.collection_id = C.collection_id
                                AND T.book_id NOT IN (SELECT U2.book_id FROM table_Book_User AS U2
                                                WHERE U2.user_id = U.user_id
                                                 AND U2.book_id = T.book_id))
GROUP BY U.user_id, C.collection_id