1

I have a query which generates a soccer standings table and although it works fine there I am trying to implement a couple of variations on it but everything I have tried has failed. I have searched and asked many times and for both problems and it seems as though a subquery would be the solution, but every attempt I have made has not given the desired result.

An example of the output can be seen at http://www.margate-fc.com/content/test/standings.php?season_id=103 for the 2010 season and http://www.margate-fc.com/content/test/standings.php?season_id=104 for the 2011 season.

I have also attached a dump of the relevant tables in a text file.

The first problem I have is creating a sum from two different tables. In the following query, the 'Pts' column is generated from the following on lines 70 and 71

, ((sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END)
+ sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END)))  AS P

The 'Adj' column is pulled from a separate table (line 80) joined by two columns (line 85)

, d.change as DIFF

LEFT JOIN deductions d ON ( d.team = t.team_id) AND (d.season = S.season_id)

Teams can have points deducted from them during a season and this needs to be reflected in the actual 'Pts' column so I need to somehow add 'P' to 'DIFF'.

As a test, I have deducted 50 points from Margate (team_id 42) for 2010 (season_id 103) and 100 points from Croydon Athletic (team_id 8) for 2011 (season_id 104) in the deductions table. These both show up correctly on the relevant seasons standings (with the links I gave back at the start) in the 'Adj' column, but everything I have tried to add 'P' to 'DIFF' has not worked.

Some of the time it works for the relevant team but the 'Pts' show up as blank for everyone else. Other times it just adds 'DIFF' to everyone. The other attempt that got me close was to show the relevant team and the right 'Pts' but no other teams showed up.

The full query is....

$i = 1;

/*ADD th, st, rd TO THE POSITION NUMBERS*/
/*
 * @return number with ordinal suffix
 * @param int $number
 * @param int $ss Turn super script on/off
 * @return string
 */
function ordinalSuffix($i, $ss=0)
{
    /*** check for 11, 12, 13 ***/
    if ($i % 100 > 10 && $i %100 < 14)
    {
        $os = 'th';
    }
    /*** check if number is zero ***/
    elseif($i == 0)
    {
        $os = '';
    }
    else
    {
        /*** get the last digit ***/
        $last = substr($i, -1, 1);

        switch($last)
        {
            case "1": 
            $os = 'st';
            break;

            case "2":
            $os = 'nd';
            break;

            case "3":
            $os = 'rd';
            break;

            default:
            $os = 'th';
        }
    }

    /*** add super script ***/
    $os = $ss==0 ? $os : '<sup>'.$os.'</sup>';

    /*** return ***/
    return $i.$os;
};

/*GET THE CORRECT SEASON*/
if (isset($_GET['season_id']) ? $_GET['season_id'] : 1);
$season_id = $_GET['season_id'];

/*COMMON STANDINGS QUERY STRINGS*/
$ht = "g.home_team = t.team_id";
$at = "g.away_team = t.team_id";
$hw = "g.home_goals > g.away_goals";
$aw = "g.home_goals < g.away_goals";
$d = "g.home_goals = g.away_goals";
$hg ="g.home_goals";
$ag ="g.away_goals";

/*THE STANDINGS QUERY*/ 
$table = mysql_query("SELECT *,
t.team_name as Tm
, @rownum := @rownum+1 AS rank
, ((sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END)
+ sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END)))  AS P
, count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D
, sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L
, SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF
, SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA
, (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END)
- SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD
, d.change as DIFF
from teams t
left join all_games g 
on t.team_id in (g.home_team,g.away_team)
LEFT JOIN seasons as S ON g.date BETWEEN S.season_start AND S.season_end
LEFT JOIN deductions d ON ( d.team = t.team_id) AND (d.season = S.season_id)
WHERE comp = '1' AND home_goals IS NOT NULL 
AND S.season_id = $season_id 
GROUP BY t.team_id
ORDER BY P desc, GD desc, GF desc 
");

The second thing I am trying to do should be extremely simple but isn't!

With this one, I want to show the standings but only taking into account the 6 most recent games for each team. The output would be something like what can be seen at http://www.andysstats.co.uk/form.asp?division=P&type=LT&season=200910 with out the right hand column.

I have followed so many different bits of advice on this and have never even got close although, once again, almost all of the advice suggested a subquery would be the solution.

The only time that I manage to get a query that did not fail or return no records it just pulled the most recent 6 games from the database and not the most recent 6 for each team.


If you are still reading this, then thank you! It was hard to decide how to word it and I thought two different posts would be frowned upon as both problems are working with the same query. Hopefully I have provided all the information needed but if there is anything else you need to know, then please let me know.

Any suggestions or pointers would be gratefully received and I would be more than happy for someone to mention it if the current query design is flawed and advise on a better approach.

Thanks in advance
Steve

Votes + Comments
Explained problem very well, Newbies must learn from him.
Attachments
-- 
-- Table structure for table `all_games`
-- 

CREATE TABLE `all_games` (
  `all_games_id` int(11) NOT NULL auto_increment,
  `date` date default NULL,
  `time` time default NULL,
  `comp` int(11) NOT NULL default '1',
  `round` tinyint(4) default NULL,
  `replay` char(1) collate latin1_general_ci default NULL,
  `home_team` int(11) NOT NULL default '1',
  `away_team` int(11) NOT NULL default '1',
  `home_goals` int(11) default NULL,
  `away_goals` int(11) default NULL,
  `attendance` int(11) default NULL,
  `report_url` varchar(255) collate latin1_general_ci default NULL,
  `photo_url` varchar(255) collate latin1_general_ci default NULL,
  `live` varchar(1) collate latin1_general_ci default NULL,
  `extratime` varchar(1) collate latin1_general_ci default NULL,
  `notes` text collate latin1_general_ci,
  `home_ht` tinyint(4) default NULL,
  `away_ht` tinyint(4) default NULL,
  `home_pen` tinyint(4) default NULL,
  `away_pen` tinyint(4) default NULL,
  PRIMARY KEY  (`all_games_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2003 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2003 ;

-- 
-- Dumping data for table `all_games`
-- 

INSERT INTO `all_games` VALUES (1, '2009-08-15', '15:00:00', 1, NULL, '0', 19, 42, 4, 0, 508, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250446274&&archive=1281131284&start_from=&ucat=10&', '', 'N', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (2, '2009-08-18', '19:45:00', 1, NULL, '0', 42, 29, 0, 4, 653, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250637449&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/6.%20mfc%20v%20Dartford%20-%2018.08.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (3, '2009-08-22', '15:00:00', 1, NULL, '0', 42, 30, 2, 1, 345, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250965567&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/7.%20mfc%20v%20Boreham%20Wood%20-%2022.08.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (4, '2009-08-24', '19:45:00', 1, NULL, '0', 1, 42, 0, 3, 243, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251151671&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (5, '2009-08-29', '15:00:00', 1, NULL, '0', 11, 42, 3, 0, 156, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251573000&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (6, '2009-08-31', '15:00:00', 1, NULL, '0', 42, 7, 0, 1, 423, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251746220&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/8.%20mfc%20v%20Cray%20Wanderers%20-%2031.08.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (7, '2009-09-05', '15:00:00', 1, NULL, '0', 31, 42, 2, 2, 120, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1252191674&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (8, '2009-09-12', '15:00:00', 2, 2, '0', 42, 19, 2, 2, 402, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1252781008&&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (9, '2009-09-15', '19:45:00', 2, 2, 'r', 19, 42, 3, 2, 301, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253050913&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (10, '2009-09-19', '15:00:00', 1, NULL, '0', 42, 15, 1, 3, 356, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253386555&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/9.%20mfc%20v%20Horsham%20-%2019.09.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (11, '2009-09-22', '19:45:00', 1, NULL, '0', 13, 42, 1, 2, 159, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253659389&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (12, '2009-09-26', NULL, 1, NULL, '0', 42, 14, 0, 0, 355, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253990544&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/10.%20mfc%20v%20AFC%20Hornchurch%20-%2026.09.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (13, '2009-10-03', NULL, 1, NULL, '0', 42, 16, 2, 1, 434, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1254597010&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/11.%20mfc%20v%20Kingstonian%20-%2003.10.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (14, '2009-10-10', NULL, 1, NULL, '0', 4, 42, 2, 0, 359, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1255198975&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/12.%20Canvey%20Island%20v%20mfc%20-%2010.10.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (15, '2009-10-17', NULL, 3, 2, '0', 22, 42, 3, 1, 265, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1255797599&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (16, '2009-11-07', NULL, 1, NULL, '0', 12, 42, 2, 1, 517, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1257626642&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (17, '2009-11-10', NULL, 5, 7, '0', 32, 42, 0, 1, 91, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1257895019&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (18, '2009-11-17', '19:45:00', 1, NULL, '0', 33, 42, 3, 4, 129, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1258495597&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (19, '2009-11-29', NULL, 1, NULL, '0', 7, 42, 3, 2, 179, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1259518899&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (20, '2009-12-05', NULL, 1, NULL, '0', 42, 11, 2, 2, 363, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260033638&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/14.%20mfc%20v%20Harrow%20Borough%20-%2005.12.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (21, '2009-12-12', NULL, 1, NULL, NULL, 35, 42, 3, 3, 316, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260614761&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (22, '2009-12-16', NULL, 5, 8, '0', 34, 42, 3, 2, 130, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260999710&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (23, '2009-12-26', NULL, 1, NULL, NULL, 18, 42, 0, 1, 225, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1261853400&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/15%20Maidstone%20United%20v%20mfc%20-%2026.12.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (24, '2010-01-02', '15:00:00', 1, NULL, '0', 42, 18, 0, 1, 568, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1262461878&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/16%2C%20mfc%20v%20Maidstone%20United%20-%2002.01.2010&Qiv=thumbs&Qis=M', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (25, '2010-01-16', NULL, 1, NULL, NULL, 42, 31, 1, 1, 280, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1263668584&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/17.%20mfc%20v%20Ashford%20Town%20%28Mx%29%20-%2016.01.2010&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (26, '2010-01-23', NULL, 1, NULL, NULL, 15, 42, 3, 3, 298, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1264267560&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (27, '2010-01-26', NULL, 4, 7, '0', 42, 36, 1, 0, 79, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1264545282&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/18.%20mfc%20v%20Chatham%20Town%20%28KSC%29%20-%2026.01.2010&Qiv=thumbs&Qis=M', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (28, '2010-01-31', NULL, 1, NULL, NULL, 16, 42, 2, 0, 347, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1264954840&archive=12
2
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by urtrivedi
1

You have explained this problem in very well manner.

I have added one more column adj, check are you getting what is expected. Here I have used ifnull function

.
.
.
, d.change as DIFF
, ((sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END)
+ sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END))) +ifnull(d.change,0)  AS adj
.
.
.
Votes + Comments
Perfect Answer
0

Absolutely perfect urtrivedi and massive thanks for that. That worked straight away. I had never come across the ifnull function before but I can see that being useful for other things I am trying to do.

That is the first problem solved. Should I mark this solved now and put the second question in a different thread?

Thanks, once again
Steve

Edited by MargateSteve: n/a

0

You may mark it solved, You may start another thread for second problem. Its little complex to get that work.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.