SQL exercise with subqueries / variables

Question

SQL exercise with subqueries / variables

Navigation

#1 by (1 votes)

1

I have a problem and I do not know how to solve it in this exercise.

It says: What people were hired by any of the companies that hired Stefanía Lopez?

The tables present are:

People:

direccion
apellido
nombre
dni
telefono
fecha_nacimiento

Companies:

nombre
cuit
e-mail

Contracts:

dni
cuit
nro_contrato
sueldo

The DNI of the contracts corresponds to the person hired and to the company where he was hired.

And the solution that I am proposing, but it does not return any record is:

select p.'nombre', p.'apellido'
from empresas e
inner join contratos cont
on e.'cuit' = cont.'cuit'
inner join personas p
on p.'dni' = cont.'dni'
where e.'cuit' in
(select cont.'cuit'
from contratos cont
inner join personas p
on p.'dni' = cont.'dni'
where p.'apellido' = 'Lopez' and p.'nombre' = 'Stefania');

I clearly have an error and I'm not seeing it. If someone can give me a hand, thank you.

mysql

asked by Damian Ricobelli 15.12.2016 в 19:16

source

1 answer

Doubt of foreach in php Android: Problems with AsyncTask when launching items

score 1 · Accepted Answer

I hope it serves you: D

select p.'nombre', p.'apellido'
from empresas e
inner join contratos cont  on e.'cuit' = cont.'cuit'
inner join personas p  on p.'dni' = cont.'dni'
inner join ( select cont.'cuit'
             from contratos cont
             where cont.'dni'' = '278090765' ) AS EMPSOFIA ON EMPSOFIA.'cuit'=cont.'cuit';