Relate tables in a single SQL Server query

Question

Relate tables in a single SQL Server query

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I need a query that returns to each map the players that had battles in it, the amount of battles that each player had (in the respective map), the total time played (by player in the respective map), and the amount of transgressions (by player on the respective map). The tables are: classification, battles and trangressor_list and are structured as follows:

classification

Id | Player_Id | Battle_Id | Time_Played

battles

Id | Map_Id

trangressor_list

Id | Transgressor_Id | Battle_Id

The one I tried was the following:

SELECT Map_Id, Player_Id, COUNT(Player_Id) AS Quantidade, SUM(Time_Played) AS 'Time Played'
FROM classification, battles, trangressor_list
WHERE  classification.Battle_Id = battles.Id AND trangressor_list.Battle_Id = battles.Id
GROUP BY Player_Id, Map_Id
ORDER BY Map_Id ASC, Player_Id ASC
;

This works partially, but it's not exactly what I need

sql-server

asked by anonymous 07.11.2017 / 20:17

2 answers

1

Considering that in a battle a player can have at most a registered violation, this is a suggestion:

-- código #1
SELECT B.Map_Id, C.Player_Id, 
       count(B.Id) as [Qtd batalhas],
       sum(C.Time_Played) as [Tempo jogado],
       count(T.Id) as [Qtd transgressões]
  from battles as B
       inner join classification as C on C.Battle_Id = B.Id
       left join trangressor_list as T on T.Transgressor_Id = C.Player_Id
                                          and T.Battle_Id = B.Id
  group by B.Map_Id, C.Player_Id;

If there is a possibility of more than one infringement per battle (for the same player), the way they are counted changes.

07.11.2017 / 23:13

PHP Save form data to array and display in another page Refresh div with checked checkbox value

score 2 · Accepted Answer

Using subqueries

SELECT subq.*
      ,QtdTransgressoes = (SELECT COUNT(DISTINCT tra.Transgressor_Id)
                             FROM classification as cla
                             JOIN trangressor_list as tra 
                                  ON tra.Battle_Id = cla.Battle_Id
                            WHERE cla.Player_Id = subq.Player_Id)
  FROM (SELECT Map_Id
              ,Player_Id
              ,COUNT(Player_Id) AS Quantidade
              ,SUM(Time_Played) AS TimePlayed
         FROM classification
         JOIN battles ON classification.Battle_Id = battles.Id
        GROUP BY Player_Id, Map_Id) as subq
  ORDER BY Map_Id ASC, Player_Id ASC

Using CTE

WITH Tab1 (Map_Id, Player_Id, Quantidade, TimePlayed)
AS
(
     SELECT Map_Id
           ,Player_Id
           ,COUNT(Player_Id) 
           ,SUM(Time_Played) 
      FROM classification
      JOIN battles ON classification.Battle_Id = battles.Id
     GROUP BY Player_Id, Map_Id
)
,
Tab2 (Player_Id, QtdTransgressoes)
AS
(
     SELECT cla.Player_Id
           ,COUNT(DISTINCT tra.Transgressor_Id)
       FROM classification as cla
       JOIN trangressor_list as tra ON tra.Battle_Id = cla.Battle_Id
      GROUP BY cla.Player_Id
 )
 SELECT Tab1.*
       ,Tab2.QtdTransgressoes
   FROM Tab1
   LEFT JOIN Tab2 ON Tab2.Player_Id = Tab1.Player_Id
  ORDER BY Map_Id ASC, Player_Id ASC