Select A Specific Customer In Mysql?

I create the following tables: create table customers ( ID varchar(9), name varchar(15), CONSTRAINT pk_id PRIMARY KEY (ID) ); create tab

Solution 1:

I prefer the SUM-CASE approach:

SELECT x.name
FROM
(SELECT c.name, SUM(CASEWHEN b.paid_out THEN0ELSE1END) all_paid
FROM customers c JOIN living_places l ON c.ID = l.ID
JOIN policies p ON l.code = p.code_living_place
JOIN bills b ON p.code_policy = b.code_policy
GROUPBY c.name) x
WHERE x.all_paid = 0;

Maybe you could avoid the nested SELECT by using the HAVING clause here also...

Solution 2:

I would approach this using aggregation and a having clause:

select p.id
from policies p join
     living_places lp
     on p.code = lp.code_living_place join
     bills b
     on b.code_policy = p.code_policy
groupby p.id
having sum(b.paid_out = 1) = count(*);