Consulta sql

[Theasker]

Tengo 2 tablas para un videoclub
peliculas
- registro (indiece)
- pelicula
- pais

clientes
- registro
- cliente
- pelicula_alquilada (relacionado con peliculas.registro)

Necesitaría saber el mayor número de peliculas alquiladas de un pais.

Por ejemplo:
España es el pais con mayor peliculas alquiladas: total 9

Lo he intentado con una subconsulta pero no lo consigo

Código SQL:

SELECT MAX(CuentaDepais) AS Expr1
FROM (
SELECT peliculas.pais, COUNT(peliculas.pais) AS CuentaDepais
FROM clientes INNER JOIN peliculas ON clientes.peli_alqui_actual = peliculas.registro
GROUP BY peliculas.pais);
 

me da un error que dice:

Código:

ERROR 1248 (42000): Every derived table must have its own alias

gracias anticipadas

[leonardo_josue]

Hola Theasker:

El error es bastante claro en su explicación, si utilizas el traductor de GOOGLE te darías cuenta que lo que dice es

Cada tabla derivada debe tener su propio alias

¿Que quiere decir? que debes un poner un ALIAS para tu subconsulta

Código:

SELECT Max(CuentaDepais) AS Expr1
FROM (
SELECT peliculas.pais, Count(peliculas.pais) AS CuentaDepais
FROM clientes INNER JOIN peliculas 
ON clientes.peli_alqui_actual = peliculas.registro
GROUP BY peliculas.pais) ALIAS;

También puedes hacerlo con una sola consulta (es decir quitando la parte del MAX), utilizando ORDER BY y LIMIT:

Código:

SELECT peliculas.pais, Count(peliculas.pais) CuentaDepais
FROM clientes INNER JOIN peliculas 
ON clientes.peli_alqui_actual = peliculas.registro
GROUP BY peliculas.pais
ORDER BY 2 DESC
LIMIT 1 

Saludos
Leo.

[Theasker]

pues ... gracias .... ha funcionado perfecto.

Tengo una duda de la segunda opción (que funciona también pefecta), la orden al final de ORDER BY 2 DESC, ¿para qué es?, ¿sería lo mismo que ORDER BY CuentaDepais DESC?

Gracias muchas :)

[leonardo_josue]

Hola de nuevo Theasker:

Tengo una duda de la segunda opción (que funciona también pefecta), la orden al final de ORDER BY 2 DESC, ¿para qué es?, ¿sería lo mismo que ORDER BY CuentaDepais DESC?

Estás en lo correcto, el poner

Código:

ORDER BY 2 DESC

es exactamente lo mismo que poner

Código:

ORDER BY CuentaDepais DESC

La sintaxis de la cláusula ORDER BY está definida de la siguiente manera:

Código:

[ORDER BY {col_name | expr | position} [ASC | DESC] , ...]

Esto quiere decir que puedes poner el nombre de una columna, una expresión O LA POSICIÓN, en este caso al poner 2 estás indicando que se ordene por el segundo campo que regrese la consulta.

Saludos
Leo.

[Theasker]

muchísimas gracias por la aclaración.

[Theasker]

Tengo ahora otra duda. Tengo que sacar las peliculas que no están alquiladas y borrarlas, primero las visualizo con:

Código MySQL:

SELECT * FROM peliculas WHERE peliculas.registro NOT IN(
SELECT clientes.peli_alqui_actual FROM peliculas INNER JOIN clientes ON clientes.peli_alqui_actual = peliculas.registro);
 

Pero cuando procedo a eliminar esos registros con:

Código MySQL:

DELETE FROM peliculas WHERE peliculas.registro NOT IN(
SELECT clientes.peli_alqui_actual FROM clientes INNER JOIN peliculas ON clientes.peli_alqui_actual = peliculas.registro);
 

Me dice:

Código:

ERROR 1093 (hy000): You can't specify target table 'peliculas' for update in FROM clause.

que entiendo que quiere decir que "no puedo poner como destino la tabla 'peliculas' en la cláusula FROM, pero entonces ¿cómo voy a construir la consulta para seleccionar los registros que quiero eliminar?

gracias anticipadas de nuevo

[gnzsoloyo]

No puedes consultar y borrar la misma tabla en la misma consulta, porque es igual que intentar cortar la rama del árbol en que estás parado.
Por otro lado, si estás haciendo un JOIN entre la misma tabla que quieres borrar con otra. ¿por qué no haces el JOIN en el DELETE?
Es algo de manual (13.2.1. Sintaxis de DELETE)
Sería el ejemplo de:

Código MySQL:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       tbl_name[.*] [, tbl_name[.*] ...]
       FROM table_references
       [WHERE where_definition]
 

Y en tu aplicación sería mas o menos así:

Código MySQL:

DELETE peliculas
FROM peliculas P INNER JOIN clientes C ON P.registro = C.peli_alqui_actual;
 

O bien:

Código MySQL:

DELETE peliculas
FROM peliculas P INNER JOIN clientes C 
WHERE P.registro = C.peli_alqui_actual;
 

Francamente no uso ninguna porque me parece modo de programar mal administrado del DELETE. Prefiero crear tablas TEMPORARY para hacer esto.

[leonardo_josue]

Hola de Nuevo:

Mucho ojo, el DELETE que propone el compañero gnzsoloyo borraría todas las películas que se hayan rentado, no las películas que no se han rentado... partiendo de la consulta que propones para obtener las películas que no se rentan:

Código MySQL:

SELECT * FROM peliculas 
WHERE peliculas.registro NOT IN(
SELECT clientes.peli_alqui_actual FROM peliculas 
INNER JOIN clientes ON clientes.peli_alqui_actual = peliculas.registro);
 

Puedes modificarla para utilizar LEFT JOIN:

Código MySQL:

SELECT peliculas.* FROM peliculas 
LEFT JOIN clientes ON clientes.peli_alqui_actual = peliculas.registro
WHERE clientes.pelicula_alqui_actual IS NULL;
 

Ahora sí, con esta forma puedes aplicar cualquiera de las formas del DELETE que propone gnzsoloyo.

Saludos
Leo.

[gnzsoloyo]

Es cierto.
No puse mucha atención en el NOT IN. Lo siento.

[Theasker]

Muchísimas gracias a los 2.

Para hacer resumen, entonces ... con INNER JOIN no puedo hacer un DELETE pero con un LEFT JOIN (y supongo que con un RIGHT JOIN) si que puedo, no lo entiendo muy bien ,ya que en este tipo de relación también estoy usando la tabla de la que voy a borrar registros.

En cuanto al comentario de lo de usar las tablas TEMPORARY, ¿cómo sería tu forma de actuar?: ¿Crear una tabla TEMPORARY con los registros que queremos borrar y luego borrarlos directamente en la tabla real? o ... ¿cómo?

Gracias por vuestra información y ayuda.

[leonardo_josue]

Hola Theasker:

con INNER JOIN no puedo hacer un DELETE pero con un LEFT JOIN (y supongo que con un RIGHT JOIN) si que puedo

No, el DELETE lo puedes hacer con INNER, LEFT o RIGTH JOIN. A lo que me refería cuando decía que estaba más aplicado el INNER era a que los registros que se iban a borrar no correspondían a lo que querías. Ejecuta estas dos consultas:

Código MySQL:

SELECT P.* 
FROM peliculas P 
INNER JOIN clientes C ON P.registro = C.peli_alqui_actual;
 
SELECT peliculas.* 
FROM peliculas 
LEFT JOIN clientes ON clientes.peli_alqui_actual = peliculas.registro
WHERE clientes.pelicula_alqui_actual IS NULL;
 

El primer SELECT te estaría regresando TODAS LAS PELÍCULAS QUE SE RENTARON, el segundo SELECT te regresaría TODAS LAS PELÍCULAS QUE NO SE HA RENTADO...

Saludos
Leo.

[Theasker]

todo entendido, ya le voy pillando el truco al tema de los join. Yo nunca usaba el left join pero .... visto lo visto es muy práctico y en ocasiones más aclaratorio que inner.

En cuanto a las temporary tables, cómo trabajais con ellas?

Muchas gracias.

[gnzsoloyo]

LEFT JOIN es muy funcional para ciertas consltas. No es que sea más claro que el INNER JOIN, sino que depende de qué es lo que se necesite obtener.
En ciertas consultas, resulta completamente inservible. En otras no. No hay uno mejor que otro.
En cuanto a las tablas de tipo TEMPORARY, yo las uso para diferentes cosas, pero entre otras cosas las uso en los stored procedure, porque como las temporary existen dentro de una conexión o sesión de MySQL, puedo tener el mismo nombre de tabla definido, sin que se produzcan conflictos entre usuarios, que sí sucedería si usase tablas fijas.
Respecto a los datos que proceso en ellas, son de todo tipo: fechas, numéricos, geográficos, etc.

[Theasker]

Muchas gracias por la respuesta y por compartir tu metodología de trabajo pero ... aun me viene muy grande todo eso que me has dicho. Estoy empezando con php y mysql, por lo que todo eso aun no puedo ponerlo en práctica, me faltan conocimientos y mucha práctica todabía.

Gracias de todas formas, espero poder entenderlo pronto