How to choose a specific client?

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:

 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 . The policy p1 paid, but p3 not.

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 do not have all paid policies. For example, invoice b5 not paid.

Answer 1

I think it should be like this:

 select name from customers where id not in ( select iving_places.id from living_places inner join policies on policies.code_living_place = living_places.code inner join bills on ( bills.code_policy = policies.code and bills.paid_out=0) ); 

Answer 2

Pretty sure about that:

 select c.ID, c.name from customers c where c.ID in ( select lp.id from policies p join living_places lp on p.code_living_place = lp.code join bills b on b.code_policy = p.code_policy group by lp.id having sum(b.paid_out = 1) = count(*)); 

It returns 0 records with the data that you currently have, and if you change one account that Louis did not pay, it will appear in the results.

Here's the SQL Fiddle to play with.

Answer 3

Do you mean this?

 select c.ID from customers c join living_places l on l.ID = c.ID join policies p on p.code_living_place = l.code join bills b on b.code_policy = p.code_policy where paid_out = 0 group by c.ID 

Answer 4

 I take it you want customer who have policies paid out ie paid_out=1? If yes, this following should do: select c.ID, c.name from customers c, living_places l, policies p where c.ID = l.ID and l.code = p.code_living_place and p.code_policy not in (select distinct b.code_policy from bills b where b.paid_out = '0') 

Here is the SQL Fiddle where you can see the results.

EDIT: this request will work if the client has one or zero policy (i.e. one or zero house). The request requires attention for> 1 home / policy.

Answer 5

Sorry for the re-entry, the answer was:

 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; 

answer from @David Bachmann Jeppesen: Select a specific client in MYSQL?