Объединить 3 таблицы в сложном запросе

Question

Объединить 3 таблицы в сложном запросе

Рейтинг: 2Ответов: 1Опубликовано: 23.10.2014

Привет всем. У меня есть три таблицы: games, active\_user_games, balance.

games

+------+---------+
|  id  |  title  |
+------+---------+
|  1   | my-game |
+------+---------+
|  2   | the are |
+----------------+

active\_user_game

+------+-----------+--------------+
|  id  |  user_id  |   game_id    | 
+------+-----------+--------------+
|  1   | 1         |     1        |
+------+-----------+--------------+
|  2   | 2         |     1        |
+------------------+--------------+
|  3   | 3         |     2        |
+------+-----------+--------------+
|  4   | 4         |     2        |
+------------------+--------------+

balance

+------+-----------+--------------+------------+
|  id  |  user_id  |   item_id    |   summ     |
+------+-----------+--------------+------------+
|  1   | 1         |     1        |   1000     |
+------+-----------+--------------+------------+
|  2   | 2         |     1        |   1000     |
+------------------+--------------+------------+
|  3   | 3         |     2        |   2000     |
+------+-----------+--------------+------------+
|  4   | 4         |     2        |   2000     |
+------------------+--------------+------------+

Мне нужно вывести все записи из таблицы games и добавить 2 столбца данных к каждой записи, которые вычисляются по данным из двух остальных таблиц. А именно мне нужно вывести количество участников из таблицы active\_user_game к конкретной игре функцией count() и сумму платежей этих участников из таблицы balance функцией sum(balance.summ).
Я делаю такой запрос:

SELECT g.id, g.title, au.game_id, au.id, b.id, b.user_id, COUNT(au.game_id) AS users,     SUM(b.summ) AS bank
FROM games AS g
  JOIN active_user_game AS au ON au.game_id = g.id
  JOIN balance AS b ON b.item_id = g.id
WHERE g.private = 0 AND g.closed = 0 AND b.type IN(5,6)
GROUP BY g.id
ORDER BY g.vip DESC, g.benefit DESC

Но так как в разных таблицах разное количество записей, он выводит users и bank с неправильными цифрами, помогите решить эту задачу. Спасибо.

sql

Источник: Stack Overflow на русском

Answer 1

▲ 2Принят

Примерно так:

SELECT g.id, g.title, 
  au.game_id, au.cnt AS users, 
  b.user_id, b.summ AS bank
FROM games AS g
  LEFT JOIN (select game_id, count(*) as cnt 
        from active_user_game 
        group by game_id) AS au ON au.game_id = g.id
  LEFT JOIN (select item_id, sum(summ) as summ 
        from balance 
        where type IN(5,6) 
        group by item_id) AS b ON b.item_id = g.id
WHERE g.private = 0 AND g.closed = 0
ORDER BY g.vip DESC, g.benefit DESC

Кроме того, au.id и b.id у вас выбирались бы по принципу, известному только mysql (вероятно первое попавшееся значение), поэтому смысла в них нет. А в других субд такой запрос просто не выполнился бы с ошибкой что-то типа "поле au.id, b.id не входят в группу".
Далее, mysql поля в выборке с одинаковыми названиями склеивает в одно поле, поэтому все три id так не получить было все равно, надо было бы пользоваться псевдонимами.

Объединить 3 таблицы в сложном запросе

Ответы