Consulta multi tabla y count()

[Heent]

Buenos días compañeros, tengo un problema con una consulta multitabla que utiliza la función COUNT().

Os cuento un poco mi estructura de datos:

Tengo una tabla que se llama 'pueblo' con los siguientes campos:
-nombre
-abreviatura

Otra tabla se llama 'amigos' y algunos de sus campos son (los que uso en el código este):
-pueblo
-nombre

Y tengo otra tabla que se llama 'fotos' y sus campos más importantes son:
-pueblo
-id

Los campos llamados "pueblo" de amigos y fotos contienen la abreviatura del pueblo de donde son. Por ejemplo podemos tener esto:

Pueblo : nombre=Barcelona, abreviatura=BCN
Amigos : nombre=Pepito, pueblo=BCN
Fotos: id=009, pueblo=BCN

Entonces yo lo que quiero hacer es recuperar, con una sola consulta en base de datos, a partir de la abreviatura del pueblo el nombre completo del pueblo así como también el total de amigos y de fotos de ese pueblo.

No hay forma que me de el resultado correcto de amigos ni fotos.

Mi consulta es:

Código MySQL:

SELECT pueblo.nombre AS pNombre, pueblo.abreviatura AS pAbrev, COUNT(amigos.nombre) AS aTotal, 
COUNT(fotos.id) AS fotoTotal FROM pueblo, amigos, fotos WHERE pueblo.abreviatura='BCN' 
AND amigos.pueblo='BCN' AND fotos.pueblo='BCN';
 

Lo he probado con la cláusula GROUP BY amigos.pueblo, fotos.pueblo pero tampoco.


Un saludo y muchas gracias!

[leonardo_josue]

Hola Heent:

En primer lugar, tienes problemas graves en tu consulta que deberías corregir. En realidad no estás especificando ninguna relación entre tus tablas, por lo tanto se estaría realizando un producto cartesiando entre las mismas... No recomiendo unir las tablas con el FROM, prefiero hacer uso del JOIN:

Código:

... 
FROM tabla1, tabla2 
WHERE tabla1.campo = tabla2.campo 
AND tabla1.campo = 'BCN'
....

Observa el elemento marcado con rojo, es justamente lo que te falta, es decir cuál es la relación que existen entre tus tablas... la condición marcada con azul sirve para filtrar la información, pero no define una unión entre tablas y por tanto se hace un producto cartesiano...

Como te dije, no recomiendo hacerlo de esta manera y prefiero el uso de JOIN...

Código:

... 
FROM tabla1 INNER JOIN tabla2 
ON tabla1.campo = tabla2.campo 
WHERE tabla1.campo = 'BCN'
....

Observa que de esta manera NO SE MEZCLAN LOS CRITERIOS DE UNIÓN (que van en la cláusula ON) CON LOS CRITERIOS DE FILTRADO (que van en la parte del WHERE)

Ahora bien, volviendo a tu problema, hay muchas formas distintas para hacer lo que quieres, siguiendo con el tema de los JOIN's podrías hacer algo como esto:

Código MySQL:

mysql> SELECT * FROM pueblo;
+-----------+-------------+
| nombre    | abreviatura |
+-----------+-------------+
| Barcelona | BCN         |
| Madrid    | MAD         |
| Otro      | OTR         |
+-----------+-------------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM amigos;
+----------+--------+
| nombre   | pueblo |
+----------+--------+
| Pepito   | BCN    |
| Leonardo | OTR    |
| Alguien  | OTR    |
+----------+--------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM fotos;
+------+--------+
| id   | pueblo |
+------+--------+
| 009  | BCN    |
| 010  | BCN    |
| 201  | MAD    |
+------+--------+
3 rows in set (0.00 sec)
 
mysql> SELECT p.abreviatura, p.nombre, a.total_amigos, f.total_fotos
    -> FROM pueblo p
    -> LEFT JOIN ( SELECT a.pueblo, COUNT(a.pueblo) total_amigos
    ->             FROM amigos a
    ->             GROUP BY a.pueblo ) a ON p.abreviatura = a.pueblo
    -> LEFT JOIN ( SELECT f.pueblo, COUNT(f.pueblo) total_fotos
    ->             FROM fotos f
    ->             GROUP BY f.pueblo ) f ON p.abreviatura = f.pueblo;
+-------------+-----------+--------------+-------------+
| abreviatura | nombre    | total_amigos | total_fotos |
+-------------+-----------+--------------+-------------+
| BCN         | Barcelona |            1 |           2 |
| MAD         | Madrid    |         NULL |           1 |
| OTR         | Otro      |            2 |        NULL |
+-------------+-----------+--------------+-------------+
3 rows in set (0.00 sec)
 

Observa que los totales se tienen que obtener por separado, ya que la relación entre pueblos-amigos y pueblos-fotos es de 1 a N. para filtrar la información sólo de un pueblo lo único que tendrías que hacer es agregar un WHERE al final de la consulta y agrega el criterio.

Dale un vistazo y si tienes alguna duda coméntala en el foro.

Saludos
Leo.