Select a specific client in MYSQL?

Question

I create the following tables:

create table customers ( ID varchar(9), name varchar(15), CONSTRAINT pk_id PRIMARY KEY (ID) ); create table living_places ( code varchar(7), ID varchar(9), CONSTRAINT pk_code PRIMARY KEY (code) ); create table policies ( code_policy varchar(7), code_living_place varchar(7), CONSTRAINT pk_code_policy PRIMARY KEY (code_policy) ); create table bills ( code varchar(7), code_policy varchar(7), paid_out boolean, CONSTRAINT pk_code_bill PRIMARY KEY (code) ); I inserted the following dates: 

I inserted the following dates:

 insert into customers(ID, name) values('fx1','Louis'); insert into customers(ID, name) values('fx2','Peter'); insert into customers(ID, name) values('fx3','Alice'); insert into living_places(code, ID) values('001','fx1'); insert into living_places(code, ID) values('002','fx2'); insert into living_places(code, ID) values('003','fx1'); insert into living_places(code, ID) values('004','fx3'); insert into policies(code_policy, code_living_place) values('p1','001'); insert into policies(code_policy, code_living_place) values('p2','002'); insert into policies(code_policy, code_living_place) values('p3','003'); insert into bills(code, code_policy, paid_out) values('b1','p1','1'); insert into bills(code, code_policy, paid_out) values('b2','p1','1'); insert into bills(code, code_policy, paid_out) values('b3','p2','0'); insert into bills(code, code_policy, paid_out) values('b4','p2','1'); insert into bills(code, code_policy, paid_out) values('b5','p3','0'); insert into bills(code, code_policy, paid_out) values('b6','p3','1'); 

Question: how to choose those people who have all their policies.

My problem is that “Louis” has two policies: “p1” and “p3”, “p1” are paid, but “p3” are not paid.

My request:

 select ID from living_places where code in ( select code from living_places where code in ( select code_living_place from policies where code_policy in ( select code_policy from bills where paid_out=1 and code_policy not in ( select code_policy from bills where paid_out=0)))); 

MySQL will return to me:

 +------+ | ID | +------+ | fx1 | +------+ 

PS: “Louis” does not have all the policies. For example, bill "b5" is not paid.

Answer 1

I prefer the SUM-CASE approach:

 SELECT x.name FROM (SELECT c.name, SUM(CASE WHEN b.paid_out THEN 0 ELSE 1 END) 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 GROUP BY c.name) x WHERE x.all_paid = 0; 

Perhaps you could avoid a nested SELECT with the HAVING clause here ...

Answer 2

I would approach this using aggregation and the having :

 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 group by p.id having sum(b.paid_out = 1) = count(*);