tablas - mysql union group by
Problemas para obtener la UNIÓN EXTERIOR IZQUIERDA para trabajar (5)
Creí entender cómo funcionan las uniones externas izquierdas, pero tengo una situación que no funciona, y no estoy 100% seguro de si la forma en que mi consulta está estructurada es incorrecta o si se trata de un problema de datos.
Para el fondo, tengo las siguientes estructuras de tabla MySQL:
mysql> describe achievement;
+-------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| id | varchar(64) | NO | PRI | NULL | |
| game_id | varchar(10) | NO | PRI | NULL | |
| name | varchar(64) | NO | | NULL | |
| description | varchar(255) | NO | | NULL | |
| image_url | varchar(255) | NO | | NULL | |
| gamerscore | smallint(5) unsigned | NO | | 0 | |
| hidden | tinyint(1) | NO | | 0 | |
| base_hidden | tinyint(1) | NO | | 0 | |
+-------------+----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
y
mysql> describe gamer_achievement;
+----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| game_id | varchar(10) | NO | PRI | NULL | |
| achievement_id | varchar(64) | NO | PRI | NULL | |
| gamer_id | varchar(36) | NO | PRI | NULL | |
| earned_epoch | bigint(20) unsigned | NO | | 0 | |
| offline | tinyint(1) | NO | | 0 | |
+----------------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
En cuanto a los datos, esto es lo que he poblado aquí (solo se incluyen columnas pertinentes por brevedad):
+----+------------+------------------------------+
| id | game_id | name |
+----+------------+------------------------------+
| 1 | 1480656849 | Cluster Buster |
| 2 | 1480656849 | Star Gazer |
| 3 | 1480656849 | Flower Child |
| 4 | 1480656849 | Oyster-meister |
| 5 | 1480656849 | Big Cheese of the South Seas |
| 6 | 1480656849 | Hexic Addict |
| 7 | 1480656849 | Collapse Master |
| 8 | 1480656849 | Survivalist |
| 9 | 1480656849 | Tick-Tock Doc |
| 10 | 1480656849 | Marathon Mogul |
| 11 | 1480656849 | Millionaire Extraordinaire |
| 12 | 1480656849 | Grand Pearl Pooh-Bah |
+----+------------+------------------------------+
12 rows in set (0.00 sec)
y
+----------------+------------+--------------+---------+
| achievement_id | game_id | earned_epoch | offline |
+----------------+------------+--------------+---------+
| 1 | 1480656849 | 0 | 1 |
| 2 | 1480656849 | 0 | 1 |
| 3 | 1480656849 | 0 | 1 |
| 4 | 1480656849 | 1149789371 | 0 |
| 7 | 1480656849 | 1149800406 | 0 |
| 8 | 1480656849 | 0 | 1 |
| 9 | 1480656849 | 1149794790 | 0 |
| 10 | 1480656849 | 1149792417 | 0 |
+----------------+------------+--------------+---------+
8 rows in set (0.02 sec)
En este caso particular, la tabla de achievement es la tabla "maestra" y contendrá la información que siempre quiero ver. La tabla gamer_achievement solo contiene información de los logros que se obtienen realmente. Para cualquier juego en particular para cualquier jugador en particular, puede haber cualquier cantidad de filas en la tabla gamer_achievement , incluyendo ninguna si no se han obtenido logros para ese juego. Por ejemplo, en los datos de muestra anteriores, los logros con los identificadores 5, 6, 11 y 12 no se han obtenido.
Lo que actualmente escribí es
select a.id,
a.name,
ga.earned_epoch,
ga.offline
from achievement a
LEFT OUTER JOIN gamer_achievement ga
ON (a.id = ga.achievement_id and a.game_id = ga.game_id)
where ga.gamer_id = ''fba8fcaa-f57b-44c6-9431-4ab78605b024''
and a.game_id = ''1480656849''
order by convert (a.id, unsigned)
pero esto solo devuelve la información completa de los logros que se han obtenido realmente: la información de logro no obtenida de la tabla de la derecha ( gamer_achievement ) no se muestra con los valores NULL como esperaría de este tipo de consulta. Esto es lo que espero ver:
+----+-------------------------------+--------------+---------+
| id | name | earned_epoch | offline |
+----+-------------------------------+--------------+---------+
| 1 | Cluster Buster | 0 | 1 |
| 2 | Star Gazer | 0 | 1 |
| 3 | Flower Child | 0 | 1 |
| 4 | Oyster-meister | 1149789371 | 0 |
| 5 | Big Cheese of the South Seas | NULL | NULL |
| 6 | Hexic Addict | NULL | NULL |
| 7 | Collapse Master | 1149800406 | 0 |
| 8 | Survivalist | 0 | 1 |
| 9 | Tick-Tock Doc | 1149794790 | 0 |
| 10 | Marathon Mogul | 1149792417 | 0 |
| 11 | Millionaire Extraordinaire | NULL | NULL |
| 12 | Grand Pearl Pooh-Bah | NULL | NULL |
+----+-------------------------------+--------------+---------+
12 rows in set (0.00 sec)
¿Que me estoy perdiendo aqui? Por lo que entiendo, la consulta básica ME PARECE correcta, pero obviamente me falta algo de información crítica.
En la cláusula WHERE, descarta algunas filas que LEFT JOIN habría rellenado con valores NULL. Desea poner la condición ga.gamer_id = ''fba8fcaa-f57b-44c6-9431-4ab78605b024'' dentro de la cláusula JOIN.
Otra opción es:
LEFT OUTER JOIN (SELECT * FROM gamer_achievement
WHERE ga.gamer_id = ''fba8fcaa-f57b-44c6-9431-4ab78605b024''
) ga
Recuerde que la unión se realiza, y en este momento, los valores NULL vienen si la condición no se puede cumplir; entonces el filtro where aplica.
Es por esta línea:
where ga.gamer_id = ''fba8fcaa-f57b-44c6-9431-4ab78605b024''
Si el gamer no ha obtenido el achievement , el valor ga.gamer_id será NULL y no calificará para la condición WHERE .
Muchos han respondido, pero lo intentaré también y, con un poco de suerte, prestaré más aclaraciones. Cómo lo he interpretado siempre (y puede consultar tantas otras publicaciones a las que he respondido con combinaciones LEFT), trato de hacer una lista de la tabla en la que quiero todo desde el principio (lado izquierdo ... por lo tanto, leo de izquierda a derecha). Luego, únete a la tabla "Otro" (lado derecho) en cualquier criterio que esté entre ellos ... Luego, al hacer una combinación izquierda, y hay criterios adicionales en la tabla lateral derecha, esas condiciones se mantendrían con esa condición de unión . Al incluirlos en la cláusula "WHERE" implicaría una unión INNER (siempre debe coincidir) que no es lo que desea ... También trato de mostrar siempre la tabla izquierda alias.field = right table alias.field para mantener la correlación claro ... Luego, aplique la cláusula where a los criterios básicos que desee de la primera tabla ... algo así como
select
a.id,
a.name,
ga.earned_epoch,
ga.offline
from
achievement a
LEFT OUTER JOIN gamer_achievement ga
ON a.id = ga.achievement_id
AND a.game_id = ga.game_id
AND ga.gamer_id = ''fba8fcaa-f57b-44c6-9431-4ab78605b024''
where
a.game_id = ''1480656849''
order by
convert (a.id, unsigned)
Observe la relación directa entre "a" y "ga" según la ID común y los valores de ID del juego, pero luego virada en el jugador específico. La cláusula where solo se preocupa por el nivel de logro externo en función del juego específico.
Supongo que la cláusula where está filtrando los resultados deseados, moverlo a la combinación izquierda puede funcionar.
select a.id,
a.name,
ga.earned_epoch,
ga.offline
from achievement a
LEFT OUTER JOIN gamer_achievement ga
ON (a.id = ga.achievement_id and
a.game_id = ga.game_id and
ga.gamer_id = ''fba8fcaa-f57b-44c6-9431-4ab78605b024'' and
a.game_id = ''1480656849'')
order by convert (a.id, unsigned)
WHERE cláusulas WHERE filtran los resultados de todo el conjunto de resultados. Si desea aplicar un filtro solo a JOIN , puede agregar la expresión a la cláusula ON .
En la siguiente consulta, he movido la expresión de filtro que se aplica a la tabla unida ( ga.gamer_id = ) de la cláusula WHERE a la cláusula ON. Esto evita que la expresión filtre las filas donde los valores de gamer_achievement son NULL.
SELECT a.id,
a.name,
ga.earned_epoch,
ga.offline
FROM achievement a
LEFT OUTER JOIN gamer_achievement ga
ON ga.achievement_id = a.id
AND ga.game_id = a.game_id
AND ga.gamer_id = ''fba8fcaa-f57b-44c6-9431-4ab78605b024''
WHERE
a.game_id = ''1480656849''
ORDER BY CONVERT(a.id, UNSIGNED)