[SOLUCIONADO] query para activos y pendientes

[Silver]

Hola,
Quizás esta sea una pregunta sencilla, pero por más pruebas que hago no doy con el resultado y ya casi se me va el tiempo de entrega del proyecto.
- Tengo una tabla "users", donde está toda la información del usuario
- Una tabla "groups" donde están los posibles grupos a los que puede pertenecer un usuario.
- Una tabla "users_groups" que enlaza "users" y "groups" donde registro los usuarios que ya están participando de un grupo determinado.
- Una tabla "pending" donde tengo los usuarios que han sido referidos, de ellos solo tengo nombre y email.
¿Qué quiero?
Quiero para cada grupo, su nombre, el número de miembros y el número de pendientes.

Los grupos y sus miembros los obtengo sin problemas con:

Código SQL:

SELECT g.`id_group`, g.`groupname`, COUNT(g.`groupname`) AS `members` 
FROM `groups` g 
LEFT JOIN `users_groups` ug ON (ug.`group_id` = g.`id_group`) 
GROUP BY g.`groupname`
 
resultado:
id_group  groupname  members
    19        aguilas         1
    20          osos           2
    18          gatos         1
 

Pero cuando quiero saber los pendientes ya me da un número equivocado:

Código SQL:

SELECT g.`id_group`, g.`groupname`, COUNT(g.`groupname`) AS `members`, COUNT(p.`name`) AS `pending` 
FROM `groups` g 
LEFT JOIN `users_groups` ug ON (ug.`group_id` = g.`id_group`) 
LEFT JOIN `pending` p ON (p.`group_id` = g.`id_group`) 
GROUP BY g.`groupname`, p.`name`
 
resultado:
id_group  groupname  members  pending
    19        aguilas           1             0
    20          osos             2             0
    18          gatos           1              1
    18          gatos           1              1
 
esperado:
id_group  groupname  members  pending
    19        aguilas           1             0
    20          osos             2             0
    18          gatos           1              2
 

¿Qué estoy haciendo mal?
Saludos y gracias!!!

[Silver]

Alguien que me pueda ayudar, por favor....

[leonardo_josue]

Hola Silver:

El problema que tienes es bastante común, y se debe al hecho de que tienes dos combinaciones 1 a n, pero al unirlas se convierten en una relación n a m, trataré de explicarme con un ejemplo:

Supongamos que tienes estos datos:

Código MySQL:

mysql> SELECT * FROM tabla1;
+------+-------------+
| id   | descripcion |
+------+-------------+
|    1 | uno         |
|    2 | dos         |
|    3 | tres        |
+------+-------------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM tabla2;
+------+-------------+
| id   | descripcion |
+------+-------------+
|    1 | uno         |
|    2 | dos         |
|    4 | cuatro      |
+------+-------------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM tabla3;
+------+-------------+
| id   | descripcion |
+------+-------------+
|    1 | uno         |
|    1 | uno         |
|    1 | uno         |
|    2 | dos         |
+------+-------------+
4 rows in set (0.00 sec)
 

Supongamos que quieres saber cuantas veces aparecen los registros de la tabla1 en las tablas dos y tres, haciéndolo por separado, no hay ningún problema:

Código MySQL:

mysql> SELECT t1.id, t1.descripcion, COUNT(t2.id) total_t2
    -> FROM tabla1 T1
    -> LEFT JOIN tabla2 T2 ON T1.id = T2.id
    -> GROUP BY T1.id, T1.descripcion;
+------+-------------+----------+
| id   | descripcion | total_t2 |
+------+-------------+----------+
|    1 | uno         |        1 |
|    2 | dos         |        1 |
|    3 | tres        |        0 |
+------+-------------+----------+
3 rows in set (0.00 sec)
 
mysql> SELECT t1.id, t1.descripcion, COUNT(t3.id) total_t3
    -> FROM tabla1 T1
    -> LEFT JOIN tabla3 T3 ON T1.id = T3.id
    -> GROUP BY T1.id, T1.descripcion;
+------+-------------+----------+
| id   | descripcion | total_t3 |
+------+-------------+----------+
|    1 | uno         |        3 |
|    2 | dos         |        1 |
|    3 | tres        |        0 |
+------+-------------+----------+
3 rows in set (0.00 sec)
 

Sin embargo, ¿Qué pasa cuando unes los dos COUNT's?

Código MySQL:

mysql> SELECT t1.id, t1.descripcion, COUNT(t2.id) total_t2, COUNT(t3.id) total_t3
    -> FROM tabla1 T1
    -> LEFT JOIN tabla2 T2 ON T1.id = T2.id
    -> LEFT JOIN tabla3 T3 ON T1.id = T3.id
    -> GROUP BY T1.id, T1.descripcion;
+------+-------------+----------+----------+
| id   | descripcion | total_t2 | total_t3 |
+------+-------------+----------+----------+
|    1 | uno         |        3 |        3 |
|    2 | dos         |        1 |        1 |
|    3 | tres        |        0 |        0 |
+------+-------------+----------+----------+
3 rows in set (0.00 sec)
 

Aquí las cuentas ya no cuadran. Para entender bien el problema observa qué pasa cuando no agrupas, es decir, colocas simplemente los LEFT JOIN's:

Código:

mysql> SELECT *
    -> FROM tabla1 T1
    -> LEFT JOIN tabla2 T2 ON T1.id = T2.id
    -> LEFT JOIN tabla3 T3 ON T1.id = T3.id;
+------+-------------+------+-------------+------+-------------+
| id   | descripcion | id   | descripcion | id   | descripcion |
+------+-------------+------+-------------+------+-------------+
|    1 | uno         |    1 | uno         |    1 | uno         |
|    1 | uno         |    1 | uno         |    1 | uno         |
|    1 | uno         |    1 | uno         |    1 | uno         |
|    2 | dos         |    2 | dos         |    2 | dos         |
|    3 | tres        | NULL | NULL        | NULL | NULL        |
+------+-------------+------+-------------+------+-------------+
5 rows in set (0.00 sec)

La relación entres tus tablas 1 y dos es 1 a n, igual que la relación entre las tablas 1 y 3, sin embargo, al ponerlas juntas, entonces creas una relación entre 2 y 3 n a m... ¿qué tienes que hacer?, agrupar primeramente las tablas 2 y tres de tal manera que sólo tengas 1 registro por cada id, entonces la relación pasa a ser una relación 1 a 1 entre las tablas... es decir, algo como esto:

Código MySQL:

mysql> SELECT t1.id, t1.descripcion, total_t2, total_t3
    -> FROM tabla1 T1
    -> LEFT JOIN ( SELECT id, descripcion, COUNT(*) total_t2
    ->      FROM tabla2
    ->      GROUP BY id, descripcion) T2 ON T1.id = T2.id
    -> LEFT JOIN ( SELECT id, descripcion, COUNT(*) total_t3
    ->      FROM tabla3
    ->      GROUP BY id, descripcion) T3 ON T1.id = T3.id
    -> GROUP BY T1.id, T1.descripcion;
+------+-------------+----------+----------+
| id   | descripcion | total_t2 | total_t3 |
+------+-------------+----------+----------+
|    1 | uno         |        1 |        3 |
|    2 | dos         |        1 |        1 |
|    3 | tres        |     NULL |     NULL |
+------+-------------+----------+----------+
3 rows in set (0.00 sec)
 

Dale un vistazo y nos comentas.

Saludos
Leo.

[Silver]

Que respuesta tan bien explicada!!! Gracias por haberte tomado el tiempo en dar una respuesta tan completa!!!
Déjame y reviso los ejemplos ahora con mis tablas y te comento que tal me fue.
Gracias!!!!!!!!!!

[Silver]

Pues sí funcionó a la perfección!! Gracias!!!