SQL ANSI: How to apply the "division operation" with 4 tables?

Question

I would like to try to fetch "all employees names that sold all books":

I've tried this:

SELECT title FROM books l1
WHERE l1.id NOT IN (
    SELECT l2.id
    FROM books l2, purchases p
    WHERE (l2.id, p.id) NOT IN (
        SELECT ip.book_id, ip.purchase_id FROM purchase_items ip
    )
)

I need to write using SQL ANSI because this query will run on MySQL, Postgres, SQL Server & Oracle.

Answer 1

Your question is a little unclear. Assuming I'm understanding correctly, you want to know which employees sold every book, regardless if it was the same purchase or different purchases. Here's one approach using aggregation:

select e.id, e.name
from employees e
  join purchases p on e.id = p.employee_id
  join purchases_items pi on p.id = pi.purchase_id
group by e.id, e.name
having count(distinct pi.book_id) = (select count(id) from books)

• Sample Fiddle Demo