tablas - Dos SQL LEFT JOINS producen resultados incorrectos

tipos de join mysql

Question

Tengo 3 tablas:

users(id, account_balance)
grocery(user_id, date, amount_paid)
fishmarket(user_id, date, amount_paid)

Tanto el grocery fishmarket como grocery mesas de grocery pueden tener múltiples apariciones para el mismo ID_usuario con diferentes fechas e importes pagados o no tener nada para un usuario determinado. Cuando intento la siguiente consulta:

SELECT
     t1."id" AS "User ID",
     t1.account_balance AS "Account Balance",
     count(t2.user_id) AS "# of grocery visits",
     count(t3.user_id) AS "# of fishmarket visits"
FROM users t1
LEFT OUTER JOIN grocery t2 ON (t2.user_id=t1."id") 
LEFT OUTER JOIN fishmarket t3 ON (t3.user_id=t1."id") 
GROUP BY t1.account_balance,t1.id
ORDER BY t1.id

Produce resultados incorrectos: "1", "12", "12" .
Pero cuando trato de LEFT JOIN a una sola tabla, produce resultados correctos para grocery visitas al grocery o al grocery fishmarket , que son "1", "3", "4" .

¿Qué estoy haciendo mal aquí?
Estoy usando PostgreSQL 9.1.

Answer 1

Es porque cuando la tabla de usuarios se une a la mesa de compras, hay 3 registros coincidentes. Luego, cada uno de esos tres registros coincide con los 4 registros en el mercado de pescado, produciendo 12 registros. Necesitas subconsultas para obtener lo que estás buscando.

Answer 2

Para su consulta original, si quita al grupo para ver el resultado preagrupado, verá por qué se crearon los conteos que estaba recibiendo.

Tal vez la siguiente consulta que utiliza subconsultas logre el resultado deseado:

SELECT
 t1."id" AS "User ID",
 t1.account_balance AS "Account Balance",
 (SELECT count(*) FROM grocery     t2 ON (t2.user_id=t1."id")) AS "# of grocery visits",
 (SELECT count(*) FROM fishmarket  t3 ON (t3.user_id=t1."id")) AS "# of fishmarket visits"
FROM users t1
ORDER BY t1.id

Answer 3

Las uniones se procesan de izquierda a derecha (a menos que los paréntesis dicten lo contrario). Si LEFT JOIN (o solo JOIN , efecto similar) tres comestibles a un usuario, obtienes 3 filas ( 1 x 3 ). Si luego te unes a 4 fishmarkets para el mismo usuario, obtienes 12 ( 3 x 4 ) filas, multiplicando el recuento anterior en el resultado, sin aumentarlo , como podrías haber esperado.
Multiplicando las visitas por comestibles y mercados de pescado por igual.

Debería funcionar así:

SELECT u.id
     , u.account_balance
     , g.grocery_visits
     , f.fishmarket_visits
FROM   users u
LEFT   JOIN (
   SELECT user_id, count(*) AS grocery_visits
   FROM   grocery
   GROUP  BY user_id
   ) g ON g.user_id = u.id
LEFT   JOIN (
   SELECT user_id, count(*) AS fishmarket_visits
   FROM   fishmarket
   GROUP  BY user_id
   ) f ON f.user_id = u.id
ORDER  BY u.id;

Para buscar valores agregados para uno o pocos usuarios, las subconsultas correlacionadas como @Vince están bien. Para una tabla completa o partes principales de ella, es (mucho) más eficiente agregar las tablas ny unirse al resultado una vez . De esta forma, tampoco necesitamos otro GROUP BY en la consulta externa.