CODEWITHSUNDEEP
sqlpostgresql
SkippyDingleChalk
SkippyDingleChalk

Reputation: 29

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 : enter image description here

Upvotes: 0

Views: 47

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

Join all 4 tables and then filter by branch city name.

select distinct c.clientid 
from client c
inner join loan_client lc on c.clientid = lc.clientid
inner join loan l on l.loan_id = lc.loan_id
inner join branch b on b.branch_id = l.branch_id
where b.city = 'Sherbrooke';

And yes, you may use natural join syntax sugar to make it shorter.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 1

Related Questions