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
LVL 1
gianitooAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.