Python241820 Python241820 - 6 months ago 12
SQL Question

how to get the ID card in MYSQL?

I created the following tables:

create table people

(
ID varchar(9),
name varchar(20),

CONSTRAINT pk_ID PRIMARY KEY (ID)
);



create table cars
(
license_plate varchar(9),
ID varchar(9),

CONSTRAINT pk_ID PRIMARY KEY (license_plate)
);




create table accidents
(
code varchar(9),
license_plate varchar(9),

CONSTRAINT pk_ID PRIMARY KEY (code)
);


I inserted the following dates:

insert into people(ID, name)
values('0x1','Louis');
insert into people(ID, name)
values('0x2','Alice');
insert into people(ID, name)
values('0x3','Peter');

insert into cars(license_plate, ID)
values('001','0x1');
insert into cars(license_plate, ID)
values('002','0x2');
insert into cars(license_plate, ID)
values('003','0x1');
insert into cars(license_plate, ID)
values('004','0x3');

insert into accidents(code, license_plate)
values('fd1','001');
insert into accidents(code, license_plate)
values('fd2','004');
insert into accidents(code, license_plate)
values('fd3','002');


The question is: how to select people who dont have had accidents in any of their cars?

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

My query:

select ID from people where ID in (select ID from cars where license_plate not in (select license_plate from accidents));


MySql:

+-----+
| ID |
+-----+
| 0x1 |
+-----+

Answer
select name from people where ID not in (
  select distinct c.ID from 
    accidents as a inner join cars as c 
    on a.license_plate = c.license_plate 
)

Explanation = the sub query will join the cars and accidents, will give you the ID's of all cars who had accidents. On this you can run not in query on the people table

Comments