League Database and Query

[Mr Rich UK]

Hi there,

 

I run a site where people play sports games against each other.

 

Now I've been looking to create a system which updates standings automatically and I think I am getting close, but I just needed a little help to get over a few problems.

 

I'm guessing it's sensible to separate the data into two tables, first one probably containing the following, let's call it 'madden_teams'

 

team_id, team_name

 

then have a table for the results, which we'll call 'madden_results'

 

result_id, away_team, away_score, home_team, home_score, week

 

I know this has something to do with joins, and I have looked into it, but I am confused by how you can use the recognition over how one result is larger than the other to get the standings.

 

So onto the query I need to do.

 

Obviously I'm going to have to do something like:

 

SELECT team_name, Sum(W), Sum(L), Sum (D)

 

To get my wins and losses, but how would I go about trying to build the rest of the query to show wins and losses correctly for each team?

 

I feel like I'm stepping up a level here and I don't really understand how you can do this.

 

I did find this http://www.phpfreaks.com/forums/index.php/topic,249774.0.html, but there was a lot here that didn't appear to work and didn't make much sense to me.  I think a lot of his code was wrong and there was stuff about 1P in there that I just didn't know what it did.

[kickstart]

Hi

 

I am sure I have got diverted down a long way of doing this, but this does work.

 

SELECT z.team_id, z.team_name , SUM(w.IfWon), SUM(x.IfLost), SUM(y.IfDraw)
FROM madden_teams z
LEFT OUTER JOIN 
(SELECT a.team_id AS TheTeamId, 1 AS IfWon
FROM madden_teams a
JOIN madden_results b
ON a.team_id = b.away_team
WHERE b.away_score > b.home_score
UNION ALL
SELECT a.team_id AS TheTeamId, 1 AS IfWon
FROM madden_teams a
JOIN madden_results b
ON a.team_id = b.home_team
WHERE b.home_score > b.away_score) w
ON z.team_id = w.TheTeamId
LEFT OUTER JOIN 
(SELECT a.team_id AS TheTeamId, 1 AS IfLost
FROM madden_teams a
JOIN madden_results b
ON a.team_id = b.away_team
WHERE b.away_score < b.home_score
UNION ALL
SELECT a.team_id AS TheTeamId, 1 AS IfLost
FROM madden_teams a
JOIN madden_results b
ON a.team_id = b.home_team
WHERE b.home_score < b.away_score) x
ON z.team_id = x.TheTeamId
LEFT OUTER JOIN 
(SELECT a.team_id AS TheTeamId, 1 AS IfDraw
FROM madden_teams a
JOIN madden_results b
ON a.team_id = b.away_team
WHERE b.away_score = b.home_score
UNION ALL
SELECT a.team_id AS TheTeamId, 1 AS IfDraw
FROM madden_teams a
JOIN madden_results b
ON a.team_id = b.home_team
WHERE b.home_score = b.away_score) y
ON z.team_id = y.TheTeamId
GROUP BY z.team_id, z.team_name

 

All the best

 

Keith

[Mr Rich UK]

Dude this is awesome. Just gave it a quick try and works perfectly.

 

I'm going to break this down so I can understand all of this now.  I need to do some extra stuff with it, but I'm sure that I will be able to work out the extra stuff I need to add to the query.

 

Basically I want to add into this points for and against, but as I said I will see if I am able to do this myself.

[Mr Rich UK]

I get that if I want to add say for instance a PointsFor part to this, for starters the beginning will have to look something like this:

 

SELECT z.team_id, z.team_name , SUM(w.IfWon), SUM(x.IfLost), SUM(y.IfDraw), SUM(u.PointsFor)

 

So then I need to add something to calculate the PointsFor

 

LEFT OUTER JOIN
(SELECT a.team_id AS TheTeamId, b.away_score AS PointsFor
FROM madden_teams a
JOIN madden_results b
ON a.team_id = b.away_team
WHERE b.away_score = b.home_score
UNION ALL
SELECT a.team_id AS TheTeamId, home_score AS PointsFor
FROM madden_teams a
JOIN madden_results b
ON a.team_id = b.home_team
WHERE b.home_score = b.away_score) y
ON z.team_id = y.TheTeamId

 

Now obviously this is wrong as this only adds the points scored when the games are a draw... not sure where to go from here.

[Mr Rich UK]

Actually, sorry your query doesn't work.

 

I just ran your query again.  There were three games put in.  The  first game team one beats team two 16-10, the next two games were draws.

 

The result I got was:

 

1  Redskins  2  NULL  2

2 Cowboys NULL 2 2

 

So the games drawn are correct, but it has given two wins to the Redskins (team one) and two wins to the Cowboys (team 2)

 

In addition to this, how do I get the NULL result to appear as a zero?  Because obviously NULL isn't great looking.  Don't understand why it would list as NULL though when technically it should be adding 0 to 0... so should get 0.

 

When I ran your query with each team winning a game and one draw, the query worked.

[kickstart]

Hi

 

Mmm, made a mess somewhere.

 

This works but is quite horrible doing subselects. However it is late and my brain hurts!

 

SELECT a.team_id, (

SELECT COUNT( result_id ) 
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score > away_score
) + ( 
SELECT COUNT( result_id ) 
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score > home_score ) AS WinCnt, (

SELECT COUNT( result_id ) 
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score < away_score
) + ( 
SELECT COUNT( result_id ) 
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score < home_score ) AS LoseCnt, (

SELECT COUNT( result_id ) 
FROM madden_results b
WHERE (
a.team_id = b.home_team
OR a.team_id = b.away_team
)
AND home_score = away_score
) AS DrawCnt
FROM madden_teams a

 

All the best

 

Keith

[Mr Rich UK]

Thanks Keith, I made a couple of amendments to that.  It worked perfectly but I changed a.team_id to a.team_name on the first line so that in the Query it returned the name of the team, rather than the ID.  Apart from that it was perfect.

 

So now I have tried to add in the Points For and Points Against into this script, and this is what I have got so far:

 

SELECT a.team_name AS Team, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score IS NOT NULL
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score IS NOT NULL ) AS Played, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score > away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score > home_score ) AS Wins, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score < away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score < home_score ) AS Losses, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE (
a.team_id = b.home_team
OR a.team_id = b.away_team
)
AND home_score = away_score
) AS Draws, (

SELECT SUM(b.away_score)
FROM madden_results b
WHERE a.team_id = b.away_team
) + (
SELECT SUM(b.home_score)
FROM madden_results b
WHERE a.team_id = b.home_team) AS PointsFor, (

SELECT SUM(b.home_score)
FROM madden_results b
WHERE a.team_id = b.away_team
) + (
SELECT SUM(b.away_score)
FROM madden_results b
WHERE a.team_id = b.home_team) AS PointsAgainst

FROM madden_teams a ORDER BY Wins DESC

 

The other addition I have put in is the games played.  As you can see I have put in that if the away score and the away id match and the box IS NOT NULL, it counts it as a game played.  Again, this works perfectly as I have a NULL result in there for a team and it is not registering in the games played column.

 

There are two things I need to clean up with this that perhaps someone can suggest.

 

At the moment if for example a team has played at home and they have scored 10 points, but they have not played away yet and the away result is NULL, then the Points For and Points Against in the query both return as NULL.  As soon as you put a value in the away_score box for this team in their other game it returns the correct points for and against.

 

Also, obviously at the beginning of the season everyone will have NULL points for and against.  Is there a way to show that as a zero, rather than NULL when the query is returned?

[Mr Rich UK]

Actually it appears that with this Query, as long as the team plays home and away and a score is entered (even zero) it works.

 

So there is something to tidy up there, as at the moment until the team plays a home and away game, it will have NULL written in both the Points For and Points Against columns.

[Mr Rich UK]

Done it now I think.  Not ideal that I can't have NULL entries into the results page (as I would like to know how to do that in future and get it to work in this system, just in case I have to do anything for soccer etc, because obviously 0-0 is a realistic score).

 

But if I do add in all unplayed games as 0-0 then this code works, I just had to change the way I worked out games played and then tell it to count games only where both the home and away scores were above zero as draws.

 

SELECT a.team_name AS Team, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score > away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score > home_score
) + (

SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score < away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score < home_score
) + (

SELECT COUNT( result_id )
FROM madden_results b
WHERE (
a.team_id = b.home_team
OR a.team_id = b.away_team
)
AND home_score = away_score AND home_score > 0 AND away_score > 0 ) AS Played, (


SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score > away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score > home_score ) AS Wins, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score < away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score < home_score ) AS Losses, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE (
a.team_id = b.home_team
OR a.team_id = b.away_team
)
AND home_score = away_score AND home_score > 0 AND away_score > 0 ) AS Draws, (


SELECT SUM(b.away_score)
FROM madden_results b
WHERE a.team_id = b.away_team
) + (
SELECT SUM(b.home_score)
FROM madden_results b
WHERE a.team_id = b.home_team) AS PointsFor, (

SELECT SUM(b.home_score)
FROM madden_results b
WHERE a.team_id = b.away_team
) + (
SELECT SUM(b.away_score)
FROM madden_results b
WHERE a.team_id = b.home_team) AS PointsAgainst

FROM madden_teams a ORDER BY Wins DESC

 

 

[Mr Rich UK]

Another update.  Getting close but I am not really struggling with this last piece.

 

I can get the query to omit any games that are in the schedule that have NULL values in home_score and away_score.

 

But when it is trying to add together the points scored by a team in it's home games and the team in its away games, if there is no points value in one of them it will return NULL for both Points and Against because obviously the SUM function is trying to add something to nothing.

 

So out of this query, could someone please help me with the points for and points against sections at the bottom, to work around the problem that if there is a NULL result in a box, it just become a zero?

 

SELECT a.team_name AS Team, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score > away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score > home_score
) + (

SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score < away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score < home_score
) + (

SELECT COUNT( result_id )
FROM madden_results b
WHERE (
a.team_id = b.home_team
OR a.team_id = b.away_team
)
AND home_score = away_score AND home_score IS NOT NULL AND away_score IS NOT NULL ) AS Played, (


SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score > away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score > home_score ) AS Wins, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.home_team
AND home_score < away_score
) + (
SELECT COUNT( result_id )
FROM madden_results b
WHERE a.team_id = b.away_team
AND away_score < home_score ) AS Losses, (

SELECT COUNT( result_id )
FROM madden_results b
WHERE (
a.team_id = b.home_team
OR a.team_id = b.away_team
)
AND home_score = away_score AND home_score IS NOT NULL AND away_score IS NOT NULL ) AS Draws, (


SELECT SUM(b.away_score)
FROM madden_results b
WHERE a.team_id = b.away_team AND away_score IS NOT NULL
) + (
SELECT SUM(b.home_score)
FROM madden_results b
WHERE a.team_id = b.home_team AND home_score IS NOT NULL ) AS PointsFor, (

SELECT SUM(b.home_score)
FROM madden_results b
WHERE a.team_id = b.away_team AND home_score IS NOT NULL
) + (
SELECT SUM(b.away_score)
FROM madden_results b
WHERE a.team_id = b.home_team AND away_score IS NOT NULL) AS PointsAgainst

FROM madden_teams a ORDER BY Wins DESC