Python241820 Python241820 - 7 months ago 9
SQL Question

how to get a specific name from customer in MYSQL?

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 accidents
(
code_accident varchar(7),
code_policy varchar(7),

CONSTRAINT pk_code_accident PRIMARY KEY (code_accident)
);


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 accidents(code_accident, code_policy)
values('A1','p1');
insert into accidents(code_accident, code_policy)
values('A2','p2');


The question is: how to select customers that dont have had accidents in any of their policies?

My problem is that when I was trying to use "not in ". Having " Louis " at least one policy in the table " accidents" , the query show me "Louis"and should not show "Louis"

My query:

create or replace view view as
select code from living_places v where code not in (
select distinct a.code_living_place from
policies as a inner join accidents as c
on a.code_policy = c.code_policy
);

select name from customers where ID in (select ID from living_places where code in (select code from view where code in (select code_living_place from policies)));


MySQL return me:

+-------+
| name |
+-------+
| Louis |
+-------+

Answer

Use not in and inner join

select name from customers 
where customers.id not in (select living_places.id 
        from living_places
        inner join policies on policies.code_living_place = living_places.code
        inner join accidents on accidents.code_policy =  policies.code_policy);