How to solve this MySQL query

Question

How to solve this MySQL query

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

3

List the name of the clients who have traveled in all the cars.

I have 4 tables, auto, driver, client and trip, the only one with foreing key is trip that acquires the driver, client and travel primary

That's how I did it but it's bad for me:

SELECT nombre
FROM cliente 
INNER JOIN viaje on cliente.nro_cliente = viaje.nro_cliente
INNER JOIN auto on viaje.patente = auto.patente
GROUP BY cliente.nombre
HAVING avg(viaje.patente) = avg(auto.patente);

Ó

SELECT nombre
FROM cliente 
INNER JOIN viaje on cliente.nro_cliente = viaje.nro_cliente
WHERE NOT EXISTS
(
SELECT patente
FROM auto
WHERE auto.patente = viaje.patente
);

HERE THE TABLES

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 15.07.2018 в 23:43

source

3 answers

1

It can also be done with HAVING as follows:

SELECT 
    c.nombre
FROM viajes v 
    INNER JOIN  auto    a  ON v.patente     = a.patente 
    INNER JOIN  cliente c  ON v.nro_cliente = c.nro_cliente 
GROUP BY  c.nro_cliente 
HAVING COUNT(DISTINCT v.patente) = (SELECT COUNT(*) FROM auto);

And, if you want to show the total of cars:

SELECT 
    c.nombre, 
    COUNT(DISTINCT v.patente) total_autos
FROM viajes v 
    INNER JOIN  auto    a  ON v.patente     = a.patente 
    INNER JOIN  cliente c  ON v.nro_cliente = c.nro_cliente 
GROUP BY  c.nro_cliente 
HAVING total_autos = (SELECT COUNT(*) FROM auto);

answered by 16.07.2018 в 03:09

0

It always works for me with the querys to think as if I was doing it by hand.

First I would consult how many cars we have (actually in the example query I put it in the having)

Then I would consult the travel table. On that would seek the total of trips made by each client where the cars are different.

I would go back to consult about this set and group by client, putting as a condition to the group that the resulting total is equal to the total of cars.

What results would be understood as the result crossing with the customer table to get the name:

WITH TT_VEHICULOS_CLIENTE_DIST AS (
   SELECT NRO_CLIENTE
        , PATENTE        
     FROM VIAJE
 GROUP BY NRO_CLIENTE
        , PATENTE
)  SELECT c.NRO_CLIENTE
        , c.NOMBRE
     FROM TT_VEHICULOS_CLIENTE_DIST t
     JOIN CLIENTE c
       ON c.NRO_CLIENTE = t.NRO_CLIENTE
 GROUP BY c.NRO_CLIENTE
        , c.NOMBRE
   HAVING COUNT(*) =  ( SELECT COUNT(*) FROM AUTO)

Of course the WITH can be replaced by a subquery but at least it makes it clearer to me to put it this way.

answered by 16.07.2018 в 01:10

Why can not I print the result of a query in angle 6? Scroll JSON array and fill select dynamically

score 1 · Accepted Answer

You need customers who have rented all cars, which requires you to determine two key facts

The total number of cars you have registered
The number of different cars each customer has rented

Knowing this, you should simply see those customers where both numbers match:

SELECT  nombre
        FROM cliente c
        -- Cantidad de autos distintos x cliente
        INNER JOIN (SELECT  nro_cliente,
                            COUNT(DISTINCT patente) AS 'CantAutos'
                            FROM viaje  
                            GROUP BY nro_cliente
              ) v
              ON v.nro_cliente = c.nrocliente
        -- Cantidada total de autos.
        WHERE v.CantaAutos = (SELECT COUNT(patente) FROM auto)