Find columns that have all values in another table

I’m having trouble with the query below. The aim is to select the clientID’s that have a loan in all of the branches in the city ‘Sherbrooke’.

WITH sherb AS (SELECT branch_id
               FROM branch
               WHERE city = 'Sherbrooke')
SELECT clientID
FROM client NATURAL JOIN loan_client NATURAL JOIN loan
WHERE branch_id = ALL (SELECT branch_id 
                       FROM sherb);

Is there any way to do this? My query should give me a result but it isn’t.

For reference, here is the relational schema provided to me :

Answer

Use aggregation:

WITH sherb AS (
      SELECT branch_id
      FROM branch
      WHERE city = 'Sherbrooke'
     )
SELECT lc.clientID
FROM loan_client lc JOIN
     loan l
     ON l.loan_id = lc.loan_id
WHERE l.branch_id IN (SELECT branch_id FROM sherb)
GROUP BY lc.clientID
HAVING COUNT(DISTINCT l.branch_id) = (SELECT COUNT(*) FROM sherb);

Note that I removed the so-called NATURAL JOIN. First, there is nothing “natural” about this join. In particular, it doesn’t use properly declared foreign key relationships.

More importantly, it just makes the query harder to debug and modify and obfuscate the logic.