How to select a specific customer?

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');

The question is: How to select those people which have all their policies paid?

My problem is that Louis has two policies p1 and p3. Policy p1 is paid but p3 is not.

My query:

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 return me:

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

P.S: Louis dont have all the policies paid. For example, the bill b5 is not paid.

Answer 1

Pretty sure it's this:

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 you currently have and if you modify the one bill Louis hasn't paid to paid then he will show up in the results.

Here's a SQL Fiddle to play around with.

Answer 2

I think should be 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 3

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 i.e 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 query will work when the customer has one or zero policy (i.e. one or zero houses). The query needs attention for > 1 house/policy.