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.
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.
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)
);
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With