Borrowers that take all loans using NOT EXISTS

Question

I want to find the borrowers who took all loan types.

Schema:

loan (number (PKEY), type, min_rating)
borrower (cust (PKEY), no (PKEY))

Sample tables:

number | type     | min_rating
------------------------------
L1     | student  | 500
L2     | car      | 550
L3     | house    | 500
L4     | car      | 700
L5     | car      | 900

cust  | no 
-----------
Jim   | L2
Tom   | L1
Tom   | L2
Tom   | L3
Tom   | L4
Tom   | L5
Bob   | L3

The answer here would be "Tom".

I can simply count the total number of loans and compare the borrower's number of loans to that, but I'm NOT allowed to (this is a homework exercise), for the purposes of this homework and learning.

I wanted to use double-negation where I first find the borrowers who didn't take all the loans and find borrowers who are not in that set. I want to use nesting with NOT EXISTS where I first find the borrowers that didn't take all the loans but I haven't been able to create a working query for that.

Valise

Think about the problem like this: There doesn't exist a loan that the borrow doesn't have. – Forcer

... which is two NOT EXISTS. – Langmuir

@GordonLinoff Yes, that's what I'm trying to do. But I started off with trying to find the customers that didn't take all the loans using SELECT b.cust FROM borrower b, loan l WHERE l.no = b.number AND NOT EXISTS (SELECT * FROM loan) and this is incorrect so I was wondering where I should fix it. – Valise

Standard solution to this relational division problem is the double not exists. Look for similar problems and answers in the "Related" div to the right --->>> – Tarsus

BTW what is the relevance of the loan.type field? do you want to select all persons with all types of loans ? – Tarsus

@Tarsus I guess they are not that important. The first column is enough. – Valise

As I said: in the related div you can find numerous examples of relational division: https://mcmap.net/q/1913984/-what-does-a-double-not-exists-clause-mean – Tarsus

@Tarsus Yes, thank you, I did look at related posts and it helped me to solve my problem using NOT EXISTS – Valise

Answer 1

A simple approach is to use the facts:

• that an outer join gives you nulls when there's no join
• coalesce() can turn a null into a blank (that will always be less that a real value)

Thus, the minimum coalesced loan number of a person who doesn't have every loan type will be blank:

select cust
from borrower b
left join loan l on l.number = b.no
group by cust
having min(coalesce(l.number, '')) > ''

The group-by neatly sidesteps the problem of selecting people more than once (and the ugly subqueries that often requires), and relies on the quite reasonable assumption that a loan number is never blank. Even if that were possible, you could still find a way to make this pattern work (eg coalesce the min_rating to a negative number, etc).

The above query can be re-written, possibly more readably, to use a NOT IN expression:

select distinct cust
from borrower
where cust not in (
  select cust
  from borrower b
  left join loan l on l.number = b.no
  where l.number is null
)

By using the fact that a missed join returns all nulls, the where clause of the inner query keeps only missed joins.

You need to use DISTINCT to stop borrowers appearing twice.

---

Your schema has a problem - there is a many-to-many relationship between borrower and load, but your schema handles this poorly. borrower should have one row for each person, and another association table to record the fact that a borrower took out a loan:

create table borrower (
    id int,
    name varchar(20)
    -- other columns about the person
);

create table borrrower_loan (
    borrower_id int, -- FK to borrower
    load_number char(2) -- FK to loan
);

This would mean you wouldn't need the distinct operator (left to you to figure out why), but also handles real life situations like two borrowers having the same name.

Ebba

Is it possible to do it with nested queries using NOT IN or NOT EXISTS? – Valise

Answer 2

I think a good first step would be to take a cartesian product* of the borrowers and the loans, then use a where clause to filter down to the ones which aren't present in your "borrowers" table. (Although I think that would use a NOT IN rather than a NOT EXISTS, so may not be exactly what you have in mind?)

(* With the caveat that cartesian products are a terrible thing to do, and you'd need to think very carefully about performance before doing this in real life)

ETA: The NOT EXISTS variant could look like this: Take the Cartesian product as before, do a correlated subquery for the combination of borrower and loan, then filter by whether this query returns any rows, using a WHERE clause with a NOT EXISTS condition.

Ardra

I think both NOT IN and NOT EXISTS can work but I'm looking for something simple and not too much correlated nesting involved if possible. – Valise

This would have one subquery, and it wouldn't be correlated. Let me edit in a little more about the NOT EXISTS alternative... – Ardra

Answer 3

select cust from borrower
except
select t.cust
from (select distinct cust,number
      from borrower cross join loan) t
left join borrower b on t.cust = b.cust and t.number = b.num
where b.num is null

Fiddle

Take a cross join of customers from borrowers and loan nums from loans. Then left join the borrower table to find customers who haven't taken all loans. Finally, use except to select the customers who have taken all loans.

Signally

Is there a way you could do it without using joins? – Valise

i will try to come up with an answer without a join – Signally