Clausula where en el COUNT() o doble consulta??

[Subotai]

Buenas a todos,

Estoy liado con una página futbolera, tengo una tabla para manejar las tarjetas rojas y amarillas que tiene la siguiente estructura:


Tabla Tarjetas
-----------------------------------
ID_TARJETA
ID_JUGADOR
ID_PARTIDO
TIPO
MINUTO

El ID_JUGADOR enlaza con la tabla de jugadores y el ID_PARTIDO con la de partidos.

Lo que quiero conseguir es que de una consulta recuperar registros que contengan el nombre del jugador, el número de tarjetas rojas y el número de tarjetas amarillas.

Hasta ahora solo he conseguido una select que funcione bien, recupero todo lo que quiero, pero con filas duplicadas cuando el mismo jugador tiene tarjetas de ambos tipos...
Es decir, que recupero el nombre del jugador y el número de amarillas, y en la siguiente fila, tengo el nombre del mismo jugador y el número de rojas.

SELECT count( TIPO ) AS numero, tarjetas.TIPO AS tarjeta, jugadores.NOMBRE AS nombre, jugadores.APELLIDOS AS apellidos
FROM tarjetas
INNER JOIN jugadores ON ( tarjetas.ID_JUGADOR = jugadores.ID_JUGADOR )
WHERE jugadores.ID_EQUIPO =24
GROUP BY tarjetas.ID_JUGADOR, tarjeta

He intentado varias cosas, pero ninguna me da resultado, si pudiera poner una cláusula del tipo COUNT ( TIPO = 'amarilla') as amarillas y otra COUNT (TIPO = 'roja') as rojas... pero no funciona o no conozco la sintaxis correcta.

He intentado con dos HAVING pero lógicamente ninguna fila de la tabla tendrá jamás roja y amarilla...


Me imagino que la solución vendría con una doble select o algo así pero no lo veo..

alguien tiene alguna idea?? Mil gracias de antebrazo ;PPP

[jurena]

A ver si esto te sirve (te he puesto sólo los campos relevantes):

Código:

SELECT j.nombre, T1.amarilla AS amarillas, T1.roja AS rojas
FROM jugadores j
INNER JOIN (

SELECT t.id_jugador, SUM( IF( t.tipo = 'amarilla', 1, 0 ) ) AS amarilla, SUM( IF( t.tipo = 'roja', 1, 0 ) ) AS roja
FROM tarjetas t
GROUP BY t.id_jugador
) AS T1 ON j.id_jugador = T1.id_jugador ORDER BY j.nombre

[Subotai]

WHOOO!!

Muy buena la select Jurena ^^ hubiera tardado días en darme cuenta del SUM y el IF
Tengo que repasar tanto del SQL >_<! lo tengo olvidado
Solo quedaba poner el ID_EQUIPO para que salieran los sancionados de ese equipo así que quedaría así:

Código:

SELECT j.NOMBRE, j.APELLIDOS, T1.AMARILLA AS amarillas, T1.ROJA AS rojas
FROM jugadores j
INNER JOIN (

SELECT t.ID_JUGADOR, SUM( IF( t.TIPO = 'amarilla', 1, 0 ) ) AS amarilla, SUM( IF( t.TIPO = 'roja', 1, 0 ) ) AS roja
FROM tarjetas t
GROUP BY t.ID_JUGADOR
) AS T1 ON j.ID_JUGADOR = T1.ID_JUGADOR
AND j.ID_EQUIPO =24
ORDER BY j.NOMBRE

Mil gracias no se que haría sin vosotros!!!!!!!!!