Dazzle Vision Dazzle Vision - 5 months ago 20
SQL Question

Using logic when it comes to using results of one query

I have a sub query below where it is able to ouput 11 teams randomly per team (1 GK, 4 DF, 4 MF and 2 FW).

SELECT * FROM(
SELECT pl.*,
ROW_NUMBER() OVER(PARTITION BY pl.TeamID, pl.Position ORDER BY NEWID()) AS Rnk
FROM(
SELECT DISTINCT p.PlayerID, p.Position, p.PlayerWeighting, p.FirstName, p.Surname, t.TeamID, t.TeamAbbreviation, f.WeekNumber
FROM dbo.Fixture f
INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID
INNER JOIN dbo.Team t ON l.LeagueID = t.LeagueID
INNER JOIN dbo.Player p ON t.TeamID = p.TeamID
WHERE f.WeekNumber = 1)
pl) po
WHERE (po.position = 'GK' and po.rnk = 1) OR
(po.position = 'DF' and po.rnk <= 4) OR
(po.position = 'MF' and po.rnk <= 4) OR
(po.position = 'FW' and po.rnk <= 2)


Below is an example of what it output:

enter image description here

I then have a fixture query: SELECT * FROM dbo.Fixture and below is what it outputs:

enter image description here

Now this is where I am struggling for logic. I want to somehow link those players within their respective teams to pit against their opposition with a fixture week. The team with the highest player weighting (based on the 11 players playing that week), will win the game. I am not sure how to actually do this though. How can I combine these players with their fixtures is simply what I'm trying to ask so I can sum up their overall player weightings to determine the winner.

UPDATE:

SELECT * FROM(
SELECT pl.*,
ROW_NUMBER() OVER(PARTITION BY pl.TeamID, pl.Position ORDER BY NEWID()) AS Rnk
FROM(
SELECT DISTINCT p.PlayerID, p.Position, p.PlayerWeighting, p.FirstName, p.Surname, t.TeamID, t.TeamAbbreviation, f.WeekNumber, SUM(PlayerWeighting)
FROM dbo.Fixture f
INNER JOIN dbo.Team t ON f.HomeTeamID = t.TeamID
INNER JOIN dbo.Player p ON t.TeamID = p.TeamID
WHERE f.WeekNumber = 1) pl) po
WHERE (po.position = 'GK' and po.rnk = 1) OR
(po.position = 'DF' and po.rnk <= 4) OR
(po.position = 'MF' and po.rnk <= 4) OR
(po.position = 'FW' and po.rnk <= 2)
GROUP BY TeamID

Answer

Why don't you SUM(PlayerWeighting) grouping by teamId on the first query and join that to dbo.Fixture by HomeTeamID or AwayTeamID? You'll get two rows by FixtureId

Select TeamID, SUM(PlayerWeighting) as TeamWeight
FROM (
    SELECT * FROM(
        SELECT pl.*,
               ROW_NUMBER() OVER(PARTITION BY pl.TeamID, pl.Position ORDER BY NEWID()) AS Rnk
        FROM(
            SELECT DISTINCT p.PlayerID, p.Position, p.PlayerWeighting, p.FirstName, p.Surname, t.TeamID, t.TeamAbbreviation, f.WeekNumber
            FROM dbo.Fixture f
            INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID
            INNER JOIN dbo.Team t ON l.LeagueID = t.LeagueID
            INNER JOIN dbo.Player p ON t.TeamID = p.TeamID
            WHERE f.WeekNumber = 1) 
            pl) po
    WHERE (po.position = 'GK' and po.rnk = 1) OR 
          (po.position = 'DF' and po.rnk <= 4) OR
          (po.position = 'MF' and po.rnk <= 4) OR
          (po.position = 'FW' and po.rnk <= 2) ) as T
GROUP BY T.TeamID
Comments