Problema para hacer una consulta

[lima1756]

Buen día, tengo un problema para realizar una consulta que me piden. Tengo esta base de datos: [URL="https://onedrive.live.com/redir?resid=5BBCACC7FF7A53E8!62609&authkey=!AITPlp 4GWMlCLpo&ithint=file%2csql"]link descarga base[/URL]

El problema que tengo es que me solicitan que diga que platillo se consumio más por especialidad (carrera).
Para este problema yo considero que se ocupan 3 tablas, la de datosalumnos, consumo y especialidad. Donde la de especialidad contiene un id_especialidad y el nombre de la misma; datosalumnos contiene los datos de los alumnos y la especialidad a la que pertenecen (llave foranea); y la tabla de consumo tiene el platillo que consumio cada Id_alumno(llave foranea).
Lo que hice fue lo siguiente, no se si sirve de algo pero es lo unico que se me ocurrio y no logre nada mas.

Código:

SELECT especialidades.NombreCarrera, consumo.Platillo, count(consumo.Platillo) as conteo
FROM especialidades, consumo LEFT JOIN datosalumnos
ON consumo.ID_Alumno = datosalumnos.ID_Alumno
WHERE especialidades.ID_Especialidad = datosalumnos.ID_Especialidad
GROUP BY especialidades.ID_Especialidad, consumo.Platillo
ORDER BY NombreCarrera,Conteo DESC

Agradeceria cualquier ayuda que me pudieran dar, muchas gracias :)

[Libras]

primero usa join explicitos en todas las tablas involucradas, segundo el link que pones no lo pude abrir......mejor pon un ejemplo de tus datos y que quieres obtener para poder ayudarte mejor, ahora que manejador de bases de datos estas usando???

[lima1756]

Hola buen día, gracias por responder, pense que se iba a poder con el link, pero aqui estan las tablas que considero involucradas (la de consumo no esta completa pero es para dar una idea y la de datosalumnos quite las columnas de nombre y apellidos porque no las considero necesario para el problema):

Tabla especialidades:
ID_Especialidad------------| NombreCarrera
1------------------------| Desarrollo Web
2------------------------| Aplicaciones
3------------------------| Moviles
4------------------------| Sistemas Operativos
5------------------------| Bases de datos

Tabla consumo:
ID ---| ID_Cafeteria-------| ID_Alumno ---| Platillo ----| Dia
1 ----| 1 ---------------| 12300077 ---| 2 ---------| 1
2 ----| 3 ---------------| 12300081 ---| 1 ---------| 4
3 ----| 1 ---------------| 12300207 ---| 2 ---------| 1
4 ----| 1 ---------------| 12300217 ---| 1 ---------| 1
5 ----| 2 ---------------| 13100034 ---| 2 ---------| 3
6 ----| 1 ---------------| 13100085 ---| 2 ---------| 1
7 ----| 2 ---------------| 13100090 ---| 6 ---------| 2
8 ----| 3 ---------------| 13300022 ---| 1 ---------| 2
9 ----| 2 ---------------| 13300036 ---| 9 ---------| 3
10 --| 3 ---------------| 13300070 ---| 9 ---------| 3
11 --|1 ----------------| 13300098 ---| 5 ---------| 4
12 --| 3 ---------------| 13300124 ---| 8 ---------| 5
13 --| 1 ---------------| 13300130 ---| 1 ---------| 6
14 --| 1 ---------------| 13300164 ---| 7 ---------| 4
15 --| 3 ---------------| 13300174 ---| 1 ---------| 4
16 --| 1 ---------------| 13300186 ---| 5 ---------| 6
17 --| 2 ---------------| 13300195 ---| 7 ---------| 1
18 --| 3 ---------------| 13300759 ---| 10 -------| 1
19 --| 4 ---------------| 13300226 ---| 1 ---------| 5
20 --| 4 ---------------| 13300279 ---| 9 ---------| 1
21 --| 2 ---------------| 12300077 ---| 6 ---------| 2
22 --| 4 ---------------| 12300081 ---| 9 ---------| 5
23 --| 1 ---------------| 12300207 ---| 1 ---------| 1
24 --| 4 ---------------| 12300217 ---| 7 ---------| 6
25 --| 2 ---------------| 13100034 ---| 7 ---------| 2
26 --| 3 ---------------| 13100085 ---| 9 ---------| 3
27 --| 2 ---------------| 13100090 ---| 9 ---------| 5
28 --| 4 ---------------| 13300022 ---| 9 ---------| 1
29 --| 3 ---------------| 13300036 ---| 1 ---------| 5
30 --| 4 ---------------| 13300070 ---| 9 ---------| 5
31 --| 4 ---------------| 13300098 ---| 9 ---------| 6
32 --| 1 ---------------| 13300124 ---| 5 ---------| 6
33 --| 2 ---------------| 13300130 ---| 6 ---------| 2
34 --| 1 ---------------| 13300164 ---| 3 ---------| 4
35 --| 4 ---------------| 13300174 ---| 1 ---------| 1
36 --| 2 ---------------| 13300186 ---| 1 ---------| 5
37 --| 1 ---------------| 13300195 ---| 5 ---------| 6
38 --| 3 ---------------| 13300759 ---| 10 --------| 3
39 --| 2 ---------------| 13300226 ---| 6 ---------| 5
40 --| 1 ---------------| 13300279 ---| 9 ---------| 1
41 --| 1 ---------------| 12300077 ---| 5 ---------| 4
42 --| 4 ---------------| 12300081 ---| 9 ---------| 5
43 --| 3 ---------------| 12300207 ---| 9 ---------| 3
44 --| 2 ---------------| 12300217 ---| 2 ---------| 2
45 --| 3 ---------------| 13100034 ---| 9 ---------| 3
46 --| 2 ---------------| 13100085 ---| 1 ---------| 2
47 --| 3 ---------------|13100090 ----| 1 ---------| 3
48 --| 4 ---------------| 13300022 ---| 7 ---------| 6
49 --| 3 ---------------| 13300036 ---| 8 ---------| 5
50 --| 3 ---------------| 13300070 ---| 8 ---------| 5

Tabla datosalumnos:
ID_Alumno -----| id_Especialidad
12300077 -----| 1
12300081 -----| 2
12300207 -----| 3
12300217 -----| 4
13100034 -----| 5
13100085 -----| 5
13100090 -----| 3
13300022 -----| 2
13300036 -----| 2
13300070 -----| 2
13300098 -----| 4
13300124 -----| 3
13300130 -----| 2
13300164 -----| 5
13300174 -----| 4
13300186 -----| 2
13300195 -----| 2
13300226 -----| 1
13300279 -----| 1
13300759 -----| 1


Lo que ocupo obtener es de cada carrera/especialidad cual fue el platillo que más se consumio (puede haber empate en los platillos y se debe de mostrar ambos platillos), algo como esto debo de obtener:

NombreCarrera ---------|PlatilloMasConsumido
Desarrollo Web ---------| 4
Aplicaciones -----------| 9
Aplicaciones -----------| 8
Moviles ---------------| 2
Sistemas Operativos ----| 3
Bases de datos ---------| 1

No se si esos sean los platillos mas consumidos, es solo como ejemplo. A demás pongo aplicaciones dos veces porque seria como si hubieran consumido 20 veces el platillo 9 y 20 veces el platillo 8 (siendo 20 el maximo consumido en esa carrera).

Como gestor de base de datos uso MySQL.

Lo que me dices de usar joins explicitos, es que haga algo como esto:

Código:

SELECT especialidades.NombreCarrera, consumo.Platillo, count(consumo.Platillo) as conteo
FROM especialidades inner join (consumo LEFT JOIN datosalumnos
ON consumo.ID_Alumno = datosalumnos.ID_Alumno)
ON especialidades.ID_Especialidad = datosalumnos.ID_Especialidad
GROUP BY especialidades.ID_Especialidad, consumo.Platillo
ORDER BY NombreCarrera,Conteo DESC

¿O a que te refieres?

De nuevo, muchas gracias :)

[mortiprogramador]

Saludo.

Pues teniendo en cuenta lo que se plantea, tal vez la mejor manera
sería modificando un poco la base de datos, pero con lo que se tiene
puede sacarse haciendo lo siguiente:

1. Una consulta que haga el conteo de cada platillo por especialidad, y que solo nos retorne el conteo más alto (esto para luego sacar los platillos que tengan esa cantidad de conteo, pues pueden ser varios en caso de empates)
2. Usar la consulta del punto 1 como condición de una consulta más grande, en donde traiga todos los platillos de una determinada especialidad.
3. Usar la consulta del punto 2 para generar UNION SELECT cambiando en cada cual la especialidad a buscar.

Es decir, algo así:

Código SQL:

-- Consulta del punto 2
SELECT e.nombre_especialidad, p.id_platillo, p.nombre_platillo, COUNT(c.id_platillo) vecesconsumidas
FROM borrar_consumo c
INNER JOIN alumno a ON c.id_alumno = a.id_alumno
INNER JOIN especialidad e ON a.id_especialidad = e.id_especialidad
INNER JOIN platillo p ON c.id_platillo = p.id_platillo
WHERE e.id_especialidad = 1
GROUP BY e.nombre_especialidad, c.id_platillo
HAVING COUNT(c.id_platillo) = 
-- Consulta del punto 1
(SELECT COUNT(c1.id_platillo)
FROM consumo c1
INNER JOIN alumno a1 ON c1.id_alumno = a1.id_alumno
INNER JOIN especialidad e1 ON a1.id_especialidad = e1.id_especialidad
WHERE e1.id_especialidad = 1
GROUP BY c1.id_platillo
ORDER BY COUNT(c1.id_platillo) DESC LIMIT 1)
-- Consulta del punto 3, que es la misma del punto 2, con otra id_especialidad
UNION
SELECT e.nombre_especialidad, p.id_platillo, p.nombre_platillo, COUNT(c.id_platillo) vecesconsumidas
FROM consumo c
INNER JOIN alumno a ON c.id_alumno = a.id_alumno
INNER JOIN especialidad e ON a.id_especialidad = e.id_especialidad
INNER JOIN platillo p ON c.id_platillo = p.id_platillo
WHERE e.id_especialidad = 2
GROUP BY e.nombre_especialidad, c.id_platillo
HAVING COUNT(c.id_platillo) = 
(SELECT COUNT(c1.id_platillo)
FROM consumo c1
INNER JOIN alumno a1 ON c1.id_alumno = a1.id_alumno
INNER JOIN especialidad e1 ON a1.id_especialidad = e1.id_especialidad
WHERE e1.id_especialidad = 2
GROUP BY c1.id_platillo
ORDER BY COUNT(c1.id_platillo) DESC LIMIT 1)
................
 

Cómo resultado, se obtendrá lo siguiente:

Código HTML:

nombre_especialidad     id_platillo     nombre_platillo     vecesconsumidas
Aplicaciones                         9                     sopa                 6
Bases de datos                     9                     sopa                 2
Desarrollo                             9                     sopa                 2
Moviles                                 9                     sopa                 2    
Moviles                                 1                     pasta                 2
Sistemas Operativos                 1                     pasta                 3
Desarrollo                             10                 fruta                     2
Desarrollo                             6                     ensalada roja     2
Bases de datos                     7                     ensalada fria         2
Bases de datos                     2                     arroz                 2
 

Y como se puede ver, pues hay empate en especialidades
y algunos platillos (Por ej en Moviles la sopa y la pasta)

Espero sirva la idea.