How to resolve this SQL query

Question

How to resolve this SQL query

Navigation

#1 by (0 votes)

0

List name and phone number of drivers who did not drive a vehicle model after the year 2010.

WHAT YOU TRY ME

SELECT c.nombre, c.telefono
FROM chofer as c 
INNER JOIN viaje as v on c.nro_chofer = v.nro_chofer
WHERE NOT EXISTS
(SELECT *
FROM auto
WHERE modelo > 2010);

MY DATABASE:

create table auto
(
patente varchar(50),
modelo varchar(50),
año int,
primary key (patente)
);

create table chofer
(
nombre varchar(50),
telefono int,
nro_chofer int,
fecha_ingreso varchar(50),
primary key (nro_chofer)
);

create table cliente
(
nombre varchar(50),
domicilio varchar(50),
nro_cliente int,
localidad varchar(50),
primary key (nro_cliente)
);

create table viaje
(
patente varchar(50),
nro_chofer int,
nro_cliente int,
fecha varchar(50),
km_totales int,
tiempo_espera varchar(50),
primary key (nro_cliente, nro_chofer, patente, fecha),
foreign key (nro_cliente) references cliente (nro_cliente),
foreign key (nro_chofer) references chofer (nro_chofer),
foreign key (patente) references auto (patente)
);

mysql sql

asked by Alan Snyder 16.07.2018 в 02:42

source

1 answer

Consume WebService with Ionic Help with android studio

score 0 · Answer 1

You can do LEFT JOIN , in table auto you also add condition modelo > 2010 , filter with NOT NULL and group by nro_chofer :

SELECT 
    ch.nombre,
    ch.telefono
FROM viajes v 
   LEFT JOIN  chofer ch ON v.nro_chofer = ch.nro_chofer 
   LEFT JOIN  auto   a  ON v.patente    = a.patente AND a.modelo > 2010
WHERE a.patente IS NOT NULL
GROUP BY  ch.nro_chofer;