• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

need help on a soccer query

I created a program where clubs can see standings for their teams.   with this query below, i can generate standings.    this will display the one who got more points on top followed by goals in favor.    

In this league there are 2 rules that i need to apply to this query.   if points are the same , then it should go by head to head competition , if both are the same (points and head to head) then it isdetermined by goal differential.  

I will assume that goal diferential can be created by ( SUM(fallgameresults.goalsinfavor) - SUM(fallgameresults.goalsagainst) )     how can i set this up in this query?     an also how can i figure out the head to  head competition in this query (this means that if team "A" played against team "B" and team "A" won the game then if it happens that they have same points in total then team "A" will be in first place)



SELECT fallgameresults.idofteam, SUM(fallgameresults.goalsinfavor)   AS goals, SUM(fallgameresults.goalsagainst) AS against,   SUM(fallgameresults.points) AS points,                           SUM(fallgameresults.win) AS win, SUM(fallgameresults.tie)   AS tie, SUM(fallgameresults.loss) AS loss, SUM(fallgameresults.gameplayed) AS played, databaseteams.id ,                        databaseteams.TeamName,databaseteams.div
FROM fallgameresults INNER JOIN                          databaseteams ON fallgameresults.idofteam = databaseteams.id    GROUP BY fallgameresults.idofteam, databaseteams.id  , databaseteams.TeamName,databaseteams.div  having databaseteams.div ='varage'
ORDER BY points desc,goals desc
0
gianitoo
Asked:
gianitoo
  • 4
  • 3
1 Solution
 
LowfatspreadCommented:
which columns on fallgameresults  specify the home and away teams?

0
 
gianitooAuthor Commented:
     [gamesid] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
      [juego] [int] NULL ,      this   is= to game number
      [idofteam] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,   this is the teamid  
      [goalsinfavor] [int] NULL ,
      [goalsagainst] [int] NULL ,
      [points] [int] NULL ,
      [win] [int] NULL ,
      [tie] [int] NULL ,
      [loss] [int] NULL ,
      [gameplayed] [int] NULL ,
      [submitted] [datetime] NULL
0
 
gianitooAuthor Commented:
In my schedule database   i   have them set up as home and away.    

[Game] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
      [Div] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [GameDate] [smalldatetime] NULL ,
      [Gametime] [smalldatetime] NULL ,
      [Site] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [HomeID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [HomeScore] [int] NULL ,
      [VisitID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [VisitScore] [int] NULL ,
      [Site1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [status] [int] NULL ,
      [statushome] [int] NULL ,
      [statusvisitor] [int] NULL ,
      [noshow] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [updateddate] [datetime] NULL
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LowfatspreadCommented:
we need to know how many times the teams have played and which won...

can schedule be related to gamesresult?

otherwise this can't be determined...

i think you only need 1 table out of schedule/gamesresult (probably schedule)

unless you normalise both and get rid of the redundancies..

how do you see the tables relating / your application working?
0
 
gianitooAuthor Commented:
could you give an idea on how would you set up the table?
0
 
gianitooAuthor Commented:
also how do i at least for now do the goal differential in my query?
0
 
LowfatspreadCommented:
Select idofteam,goals,against,goals - against as diff,points
         , win,tie,loss,played,id,teamname
--  into #temp
  from (
         SELECT g.idofteam, SUM(g.goalsinfavor)   AS goals, SUM(g.goalsagainst) AS against
              , SUM(g.points) AS points
              , SUM(g.win) AS win
              , SUM(g.tie)   AS tie
              , SUM(g.loss) AS loss
              , SUM(g.gameplayed) AS played
              , t.id
             ,  t.TeamName
           FROM fallgameresults as g
          INNER JOIN databaseteams as t
             ON g.idofteam = t.id  
          Where t.div ='varage'
          GROUP BY g.idofteam,t.id,t.teamname
       ) as x  
ORDER BY points desc,4 desc


0
 
jawad_ashrafCommented:
hi gianitoo
                to me the table design seems to   unnormalized.
there are two entities, one team  , two game. now one team can play more than one game
and one game includes more than one team , so the relation ship between them is many to many, inthis case a third table which will be a sandwitch table between these two tables which u have shown.

table TEAM
team_id
team_name

table GAME

game_id
game_date
game_time
.
.
.

table TEAM_GAME

game_id
team_id
goal_in_favour
goal_against
status
points
.
.
.

i dont have much time right now, try to change it then apply query,
 let me nknow if i m sure about ur question

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now