Problema query

[Hypocrisy]

Buenas, soy nuevo por aqui y tambien soy nuevo en el mundillo del sql.
A ver si me podeis hechar un cable con esta query, de momento pego la consulta que he realizado:

select C.codi_empl,E.nom_empl,nom_rest, num_estrelles
from empleats E, empl_rest C, restaurants R
where E.codi_empl = C.codi_empl
and C.codi_rest=R.codi_rest
UNION
select DISTINCT codi_empl,nom_empl,nom_rest,num_estrelles
from empleats,restaurants
where codi_empl not in (select codi_empl
from empl_rest);

esta consulta trata de buscar los empleados contratados en los restaurantes y tambien tengo que sacar los no contratados
Los empleados contratados los saco correctamente, el problema viene cuando quiero sacar los no contratados, ya que me sale el nombre repetidas varias veces por cada restaurante, la salida es esta:
1 Maria Fernandez El Bulli 3
2 Jose Alvarez El Bulli 3
3 Salvador Ramirez El Bulli 3
4 Maria Gimenez La bella trattoria 0
5 Elena Fuster La bella trattoria 0
6 Daniel Borrell La Gran Muralla 0
7 Ramon Casas Restaurant Pekin 1
8 Elena Garcia Restaurant Pekin 1
9 Ivan Roure Restaurant Pekin 1
10 Marta Casademont Sakura 2
11 Juan Pujol La patagonia 1
12 Pedro Ripoll Restaurant Buenos Aires 0
13 Esther Gimenez Can Fabes 1
13 Esther Gimenez El Bulli 3
13 Esther Gimenez Fiore 2
13 Esther Gimenez La Dolce Vita 0
13 Esther Gimenez La Gran Muralla 0
13 Esther Gimenez La bella trattoria 0
13 Esther Gimenez La patagonia 1
13 Esther Gimenez Restaurant Buenos Aires 0
13 Esther Gimenez Restaurant Pekin 1
13 Esther Gimenez Sakura 2
13 Esther Gimenez Sant Pau 3

Como hago para que solo me salga el nombre del empleado no contratado???Llevo toda la tarde intentandolo y "na de na"

Saludoooos

[gnzsoloyo]

¿Has probado algo así?:

Código SQL:

SELECT DISTINCT
  C.codi_empl,
  C.nom_empl,
  IF(nom_rest IS NULL, 'NO CONTRATADO', nom_rest) nom_rest,
  IF(nom_rest IS NULL, '', num_estrelles) num_estrelles
FROM
  empl_rest C
  LEFT JOIN empleats E USING(codi_empl)
  LEFT JOIN restaurants R USING(codi_rest);
 

Eso debería devolver todos los empleados de la base, con sus nombres )siempre y cuando la tabla EMPL_REST contenga efectivamente el nombre, y devolver "NO CONTRATADO", si no está vinculado a ningún restaurante (según lo que se infiere) que figure en la tabla EMPLEATS.

[Hypocrisy]

Iniciado por gnzsoloyo

¿Has probado algo así?:

Código SQL:

SELECT DISTINCT
  C.codi_empl,
  C.nom_empl,
  IF(nom_rest IS NULL, 'NO CONTRATADO', nom_rest) nom_rest,
  IF(nom_rest IS NULL, '', num_estrelles) num_estrelles
FROM
  empl_rest C
  LEFT JOIN empleats E USING(codi_empl)
  LEFT JOIN restaurants R USING(codi_rest);
 

Eso debería devolver todos los empleados de la base, con sus nombres )siempre y cuando la tabla EMPL_REST contenga efectivamente el nombre, y devolver "NO CONTRATADO", si no está vinculado a ningún restaurante (según lo que se infiere) que figure en la tabla EMPLEATS.

Se me olvido decir que los empleados no contratados no tienen ningun null para el asignamiento de restaurante..directamente no tiene ningun codigo de asignamiento (nose si me explico) Ejemplo de la tabla:
Empleados:
codi, nom, data naixement, sexe, codi pais
INSERT INTO Empleats VALUES(11,"Juan Pujol","01-09-1967","M",1);
INSERT INTO Empleats VALUES(12,"Pedro Ripoll","05-16-1974","M",4);
INSERT INTO Empleats VALUES(13,"Esther Gimenez","09-23-1977","W",3);
INSERT INTO Empleats VALUES(14,"José Gomez","08-21-1970","M",7);

Empleados contratados:
codi restaurant, codi empleat, codi categoria, data alta
INSERT INTO Empl_rest VALUES(7,9,5,"03-30-2008");
INSERT INTO Empl_rest VALUES(10,10,3,"03-14-1997");
INSERT INTO Empl_rest VALUES(11,11,5,"12-07-1994");
INSERT INTO Empl_rest VALUES(12,12,5,"11-02-2001");

COMMIT WORK;

Como veis los empleados 13 y 14 no estan "contratados"


Yo lo unico que quiero es que salga el nombre y el apellido de los dos empleados no contratados y que no salgan los nombres de los restaurantes.

[gnzsoloyo]

Se me olvido decir que los empleados no contratados no tienen ningun null para el asignamiento de restaurante..directamente no tiene ningun codigo de asignamiento (nose si me explico)

Eso no tiene ninguna importancia, porque el NULL aparece cuando realizas un LEFT JOIN entre dos tablas y en la segunda tabla aparecen solamente una parte de los items relacionados de la primera.
Es un asunto de SQL, no de contenido de tablas.

Me explico:
Cuando realizas un JOIN simple entre dos tablas, el DBMS relaciona cada registro de la primea tabla con cada registro de la segunda, donde el contenido de los campos del mismo nombre sea el mismo (motivo por el cual tu consulta original adolece de algunos problemas semánticos que pueden generar un producto cartesiano). Cuando sólo debe considerar uno o algunos campos para igualar se debe indicar en el FROM con la cláusula ON tabla1.campo = tabla2.campo.
Ahora bien, ese es el funcionamiento del JOIN. En el caso del INNER JOIN devovlerá sólo los coincidentes en el ON, realizando la eliminación de los que no poseen coincidencia.
En cambio, cuando se realiza un LEFT JOIN, se le está indicando que devuelva todos los registros de la tabla izquierda y también puede devolver los registros con que coinciden de la tabla derecha; pero el funcionamiento no es excluyente: Cuando un registro de la tabla izquierda no concide, de todos modos lo devuelve, sólo que los restantes campos contienen valores NULL, porque no poseen coincidencias.
El RIGHT JOIN hace exactamente lo mismo, pero con la tabla derecha.

Ahora bien, la lógica de mi consulta se basa en suposiciones inferidas en base a la consulta original: Tienes una tabla que conserva todos los empleados posibles, otra tabla que contiene los restaurantes y una tercera tabla que establece qué empleados trabajan en qué restaurantes.
De esta forma, la consulta propuesta cruza los datos de la primera tabla y la tercera, obteniendo el listado de todos los empleados, y los ID de los restaurantes donde trabajan o NULL en esa columna si no lo tienen. El segundo LEFT JOIN sirve para obtener los nombres de los restaurantes, en aquellos casos en que el valor de ID del restaurante no es un NULL.
En definitiva, esa es la lógica de lo que te propongo.
Lo que si, para que funcione un poco mejor, posiblemente la sintaxis fuera:

Código sql:

SELECT DISTINCT
  C.codi_empl,
  C.nom_empl,
  IF(nom_rest IS NULL, 'NO CONTRATADO', nom_rest) nom_rest,
  IF(nom_rest IS NULL, '', num_estrelles) num_estrelles
FROM
  empl_rest C
  LEFT JOIN empleats E USING(codi_empl)
  LEFT JOIN restaurants R USING(codi_rest)
WHERE 
  codi_empl IS NOT NULL OR codi_rest IS NULL;
 

Lo que no debes confundir en este caso es lo que tienes en las tablas con lo que obtienes en las consultas. En casos como el LEFT JOIN hay que conocer la lógica de su funcionamiento para estar seguro de qué es lo que se obtendrá.

[Hypocrisy]

bua...muchas gracias..me has dejado clarisiimas las cosas...ya me funciona la query como queria :), he modificado unas cosillas y ha quedado asi (funciona):

Código:

SELECT DISTINCT E.codi_empl,E.nom_empl, CASE
WHEN nom_rest IS NULL THEN ''
else nom_rest 
  	end
nom_rest,num_Estrelles
from empl_rest C
RIGHT OUTER JOIN empleats E on (E.codi_empl = C.codi_empl)
left JOIN restaurants R on ( C.codi_rest=R.codi_rest);

Ahora tengo otro problemilla:
Tngo que obtener la cantidad de empleados de cada restaurantes..pero hay restaurantes que no tienen empleados contratados. Tiene que imprimir pon pantalla todos los restaurantes con la cantidad de empleados y los restaurantes que no tengan debe salir 0.
De momento puedo sacar los restaurantes con empleados pero no puedo sacar los restaurantes con no empleados y que salga 0.
El codigo de momento es este:

Código:

select r.nom_rest, count(*) as num_empl_per_restaurant
from restaurants r join empl_rest t on (r.codi_rest=t.codi_rest)
group by t.codi_rest,r.nom_rest;

ire probando a ver...

[gnzsoloyo]

Código SQL:

SELECT r.nom_rest, COUNT(*) AS num_empl_per_restaurant
FROM restaurants r LEFT JOIN empl_rest t ON (r.codi_rest=t.codi_rest)
WHERE r.nom_rest IS NOT NULL
GROUP BY t.codi_rest;
 

No te olvides que para que se cuente cero (0) debes obtener los registros en NULL, que son los que te darán cero.
Como NULL no lo puede contar, devolverá cero en ese caso.
Otro detalle sería que si el código de restaurant es siempre único, agrupar por nombre es superfluo.

[Hypocrisy]

Iniciado por gnzsoloyo

Código SQL:

SELECT r.nom_rest, COUNT(*) AS num_empl_per_restaurant
FROM restaurants r LEFT JOIN empl_rest t ON (r.codi_rest=t.codi_rest)
WHERE r.nom_rest IS NOT NULL
GROUP BY t.codi_rest;
 

No te olvides que para que se cuente cero (0) debes obtener los registros en NULL, que son los que te darán cero.
Como NULL no lo puede contar, devolverá cero en ese caso.
Otro detalle sería que si el código de restaurant es siempre único, agrupar por nombre es superfluo.

eso lo tenia hecho ya...pero el problema que tengo es que todos aquellos restaurantes que no tengan restaurantes salga 0...pero en vez de salir 0 sale 1...
He hecho una case para que todos los que sean null..sean 0... pero sale asi:
Como hago para que en la columna Emple cambien los 1 por 0? Se puede introducir un count(*) dentro de un case? Lo he probado pero es imposible.
kiero hacer algo como:
case when t.codi_rest is null then 0 else count(*) end...pero nada

Restaurante .......................Emple Emple
Shangai..................................1........ 0
La Dolce Vita...........................1........0
Restaurant Buenos Aires ........1
El Bulli.....................................3
Restaurant Pekin....................3
Sant Pau.................................1........0
La bella trattoria.....................2
Fiore.......................................1..... ...0
La Gran Muralla.......................1
Sakura.....................................1
Can Fabes...............................1.......0
La patagonia...........................1
Codigo:

Código:

SELECT r.nom_rest, COUNT(*) AS num_empl_per_restaurant, case
when t.codi_rest is NULL then 0
end  
FROM restaurants r LEFT JOIN empl_rest t ON (r.codi_rest=t.codi_rest)
WHERE r.nom_rest IS NOT NULL
GROUP BY t.codi_rest,r.nom_rest;

Como hago para que en la columna Emple cambien los 1 por 0? Se puede introducir un count(*) dentro de un case?

Quiero hacer algo como:
case when t.codi_Rest is null then 0 else count(*) end...pero no sale

[gnzsoloyo]

Código sql:

SELECT r.nom_rest, SUM(IF(t.codi_rest IS NULL, 0, 1)) CantEmpleados
FROM restaurants r LEFT JOIN empl_rest t ON (r.codi_rest=t.codi_rest)
WHERE r.nom_rest IS NOT NULL
GROUP BY r.codi_rest;
 

Ojo: Esto es un truco no documentado .

[Hypocrisy]

Iniciado por gnzsoloyo

Código sql:

SELECT r.nom_rest, SUM(IF(t.codi_rest IS NULL, 0, 1)) CantEmpleados
FROM restaurants r LEFT JOIN empl_rest t ON (r.codi_rest=t.codi_rest)
WHERE r.nom_rest IS NOT NULL
GROUP BY r.codi_rest;
 

Ojo: Esto es un truco no documentado .

WTF O_o eso es nuevo O_o. Lo he modificado para que me funcionase ...uoalaaa que alegriaaa . Eres el pu*o amo XD.
Pego codigo modificado:

Código:

SELECT r.nom_rest, SUM(CASE WHEN T.codi_rest is null then 0 else 1 End) CantEmpleados
   
      FROM restaurants r LEFT JOIN empl_rest t ON (r.codi_rest=t.codi_rest)
   
      WHERE r.nom_rest IS NOT NULL
   
      GROUP BY r.codi_rest,r.nom_rest;

Blanco y en botella ;)
Por cierto, me lo podrias explicar mas o menos que hace SUM...veo como funciona pero no se muy bien que es.