Merging two queries

Question

MargateSteve 12 Light Poster

13 Years Ago

I currently have two queries that separately show goals scored and appearances made by players in a football team but need to bring them together in one query so I can aggregate results against each other, as well as output records from both in one html table.

The two queries are

SELECT *,
COUNT(CASE games.competition WHEN 1 THEN 1 ELSE NULL END) AS lgegls,
COUNT(CASE games.competition WHEN 2 THEN 1 ELSE NULL END) AS facgls,
COUNT(CASE games.competition WHEN 3 THEN 1 ELSE NULL END) AS fatgls,
COUNT(CASE games.competition WHEN 4 THEN 1 ELSE NULL END) AS kscgls,
COUNT(CASE games.competition WHEN 5 THEN 1 ELSE NULL END) AS lgecgls,
COUNT(goals.goal_id) AS tgls
FROM goals, games 
INNER JOIN players ON goals.scorer = players.player_id
WHERE goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY players.player_id

and

SELECT *,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 1 THEN 1 ELSE NULL END) AS lgest,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 2 THEN 1 ELSE NULL END) AS lgesub,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 3 THEN 1 ELSE NULL END) AS lgebench,
COUNT(CASE WHEN games.competition = 2 AND appearances.type = 1 THEN 1 ELSE NULL END) AS facst,
COUNT(CASE WHEN games.competition = 2 AND appearances.type = 2 THEN 1 ELSE NULL END) AS facsub,
COUNT(CASE WHEN games.competition = 2 AND appearances.type = 3 THEN 1 ELSE NULL END) AS facbench,
COUNT(CASE WHEN games.competition = 3 AND appearances.type = 1 THEN 1 ELSE NULL END) AS fatst,
COUNT(CASE WHEN games.competition = 3 AND appearances.type = 2 THEN 1 ELSE NULL END) AS fatsub,
COUNT(CASE WHEN games.competition = 3 AND appearances.type = 3 THEN 1 ELSE NULL END) AS fatbench,
COUNT(CASE WHEN games.competition = 4 AND appearances.type = 1 THEN 1 ELSE NULL END) AS kscst,
COUNT(CASE WHEN games.competition = 4 AND appearances.type = 2 THEN 1 ELSE NULL END) AS kscsub,
COUNT(CASE WHEN games.competition = 4 AND appearances.type = 3 THEN 1 ELSE NULL END) AS kscbench,
COUNT(CASE WHEN games.competition = 5 AND appearances.type = 1 THEN 1 ELSE NULL END) AS lcst,
COUNT(CASE WHEN games.competition = 5 AND appearances.type = 2 THEN 1 ELSE NULL END) AS lcsub,
COUNT(CASE WHEN games.competition = 5 AND appearances.type = 3 THEN 1 ELSE NULL END) AS lcbench,
COUNT(CASE WHEN appearances.type = 1 THEN 1 ELSE NULL END) AS tst,
COUNT(CASE WHEN appearances.type = 2 THEN 1 ELSE NULL END) AS tsub,
COUNT(CASE WHEN appearances.type = 3 THEN 1 ELSE NULL END) AS tbench,
SUM(CASE WHEN games.competition = 1 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 1 THEN appearances.on ELSE NULL END) AS lgemins,
SUM(CASE WHEN games.competition = 2 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 2 THEN appearances.on ELSE NULL END) AS facmins,
SUM(CASE WHEN games.competition = 3 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 3 THEN appearances.on ELSE NULL END) AS fatmins,
SUM(CASE WHEN games.competition = 4 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 4 THEN appearances.on ELSE NULL END) AS kscmins,
SUM(CASE WHEN games.competition = 5 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 5 THEN appearances.on ELSE NULL END) AS lcmins,
SUM(appearances.off) - SUM(appearances.on) AS tmins
FROM appearances, games 
INNER JOIN players ON appearances.player = players.player_id
WHERE appearances.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6 AND players.player_id <> 1
GROUP BY players.player_id

I have placed the tables and some sample data, at the end of the post.

Both queries work fine on their own but I need to merge them to get the required results.

The one extra relationship that is not shown there, that would probably be required in the joint query is that 'goals.scorer = appearances.player', although that may not be relevant as they are both FK's to 'players.player_id'.

I want to out put all of the results from both queries in one html table. As I said, they both work perfectly individually but no matter how much I read up on joins, I cannot work out how to join them.

One other thing I would need to do is perform aggregates between what is currently the two queries, for example, SUM(tmins / tgls) but I would imagine that would be straightforward once everything is joined correctly.

I have been working on this for over a month at PHPFreaks and this is one of the very few times that one of my questions on there have not been solved so started to wonder if it was possible, but have seen similar things working elsewhere.

Any help, advice, suggestions or rewrites would be gratefully received.

Thanks in advance
Steve

CREATE TABLE `games` (
  `match_id` int(11) NOT NULL auto_increment,
  `date` date default NULL,
  `time` time default NULL,
  `competition` int(11) default NULL,
  `round` tinyint(2) default NULL,
  `replay` char(1) default NULL,
  `h_a` varchar(45) default NULL,
  `opponent` int(11) default NULL,
  `wdl` varchar(45) default NULL,
  `for` tinyint(4) default NULL,
  `against` tinyint(4) default NULL,
  `attendance` int(11) default NULL,
  `report_url` longtext,
  `photo_url` longtext,
  `stadium` int(11) default NULL,
  `manager` varchar(45) default NULL,
  `live` varchar(255) default NULL,
  `notes` varchar(255) default NULL,
  `extra_time` char(1) default NULL,
  PRIMARY KEY  (`match_id`)
) TYPE=MyISAM AUTO_INCREMENT=312 ;


INSERT INTO `games` VALUES (1, '2009-08-15', '15:00:00', 1, NULL, '', 'A', 19, 'L', 0, 4, 508, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250446274&&archive=1281131284&start_from=&ucat=10&', '', 4, '', 'N', '', '');
INSERT INTO `games` VALUES (2, '2009-08-18', '19:45:00', 1, NULL, '', 'H', 29, 'L', 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', 7, 'TERRY YORATH', NULL, NULL, NULL);
INSERT INTO `games` VALUES (3, '2009-08-22', '15:00:00', 1, NULL, '', 'H', 30, 'W', 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', 7, 'TERRY YORATH', NULL, NULL, NULL);
INSERT INTO `games` VALUES (4, '2009-08-24', '19:45:00', 1, NULL, '', 'A', 1, 'W', 3, 0, 243, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251151671&archive=1281131284&start_from=&ucat=10&', NULL, 2, 'TERRY YORATH', NULL, NULL, NULL);
INSERT INTO `games` VALUES (5, '2009-08-29', '15:00:00', 1, NULL, '', 'A', 11, 'L', 0, 3, 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, 'TERRY YORATH', NULL, NULL, NULL);
INSERT INTO `games` VALUES (6, '2009-08-31', '15:00:00', 1, NULL, '', 'H', 7, 'L', 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, 'TERRY YORATH', NULL, NULL, NULL);
INSERT INTO `games` VALUES (7, '2009-09-05', '15:00:00', 1, NULL, '', 'A', 31, 'D', 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, 9, 'TERRY YORATH', NULL, NULL, NULL);
INSERT INTO `games` VALUES (8, '2009-09-12', '15:00:00', 2, 2, '', 'H', 19, 'D', 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&', '', NULL, 'TERRY YORATH', '', '', '');
INSERT INTO `games` VALUES (9, '2009-09-15', '19:45:00', 2, 2, 'r', 'A', 19, 'L', 2, 3, 301, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253050913&archive=1281131284&start_from=&ucat=10&', '', NULL, 'TERRY YORATH', '', '', '');
INSERT INTO `games` VALUES (10, '2009-09-19', '15:00:00', 1, NULL, '', 'H', 15, 'L', 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, 'TERRY YORATH', NULL, NULL, NULL);
INSERT INTO `games` VALUES (11, '2009-09-22', '19:45:00', 1, NULL, '', 'A', 13, 'W', 2, 1, 159, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253659389&archive=1281131284&start_from=&ucat=10&', '', NULL, 'TERRY YORATH', '', '', '');
INSERT INTO `games` VALUES (12, '2009-09-26', NULL, 1, NULL, '', 'H', 14, 'D', 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, 'NEVILLE SOUTHALL', NULL, NULL, NULL);
INSERT INTO `games` VALUES (13, '2009-10-03', NULL, 1, NULL, '', 'H', 16, 'W', 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, 'NEVILLE SOUTHALL', NULL, NULL, NULL);
INSERT INTO `games` VALUES (14, '2009-10-10', NULL, 1, NULL, '', 'A', 4, 'L', 0, 2, 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, 'NEVILLE SOUTHALL', NULL, NULL, NULL);
INSERT INTO `games` VALUES (15, '2009-10-17', NULL, 3, 2, '', 'A', 22, 'L', 1, 3, 265, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1255797599&archive=1281131284&start_from=&ucat=10&', '', NULL, 'NEVILLE SOUTHALL', '', '', '');
INSERT INTO `games` VALUES (16, '2009-11-07', NULL, 1, NULL, '', 'A', 12, 'L', 1, 2, 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, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (17, '2009-11-10', NULL, 5, 7, '', 'A', 32, 'W', 1, 0, 91, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1257895019&archive=1281131284&start_from=&ucat=10&', '', NULL, 'MARK BUTLER', '', '', '');
INSERT INTO `games` VALUES (18, '2009-11-17', '19:45:00', 1, NULL, '', 'A', 33, 'W', 4, 3, 129, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1258495597&archive=1281131284&start_from=&ucat=10&', '', NULL, 'MARK BUTLER', '', '', '');
INSERT INTO `games` VALUES (19, '2009-11-29', NULL, 1, NULL, '', 'A', 7, 'L', 2, 3, 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, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (20, '2009-12-05', NULL, 1, NULL, '', 'H', 11, 'D', 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, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (21, '2009-12-12', NULL, 1, NULL, NULL, 'A', 35, 'D', 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, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (22, '2009-12-16', NULL, 5, 8, '', 'A', 34, 'L', 2, 3, 130, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260999710&archive=1281131284&start_from=&ucat=10&', '', NULL, 'MARK BUTLER', '', '', '');
INSERT INTO `games` VALUES (23, '2009-12-26', NULL, 1, NULL, NULL, 'A', 18, 'W', 1, 0, 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, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (24, '2010-01-02', '15:00:00', 1, NULL, '', 'H', 18, 'L', 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', 7, 'MARK BUTLER', '', '', '');
INSERT INTO `games` VALUES (25, '2010-01-16', NULL, 1, NULL, NULL, 'H', 31, 'D', 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, 'MARK BUTLER', NULL, NULL, NULL);
INSERT INTO `games` VALUES (26, '2010-01-23', NULL, 1, NULL, NULL, 'A', 15, 'D', 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, 'MARK BUTLER', NULL, NULL, NULL);

CREATE TABLE `goals` (
  `goal_id` int(11) NOT NULL auto_increment,
  `match` int(11) default NULL,
  `scorer` int(11) default NULL,
  `goal_type` int(11) default NULL,
  `goal_time` int(11) default NULL,
  PRIMARY KEY  (`goal_id`)
) TYPE=MyISAM AUTO_INCREMENT=123 ;

INSERT INTO `goals` VALUES (1, 3, 48, 1, 90);
INSERT INTO `goals` VALUES (2, 3, 53, 1, 49);
INSERT INTO `goals` VALUES (3, 4, 6, 1, 23);
INSERT INTO `goals` VALUES (4, 4, 6, 1, 33);
INSERT INTO `goals` VALUES (5, 4, 38, 1, 73);
INSERT INTO `goals` VALUES (6, 7, 6, 2, 34);
INSERT INTO `goals` VALUES (7, 7, 68, 1, 23);
INSERT INTO `goals` VALUES (8, 8, 8, 1, 41);
INSERT INTO `goals` VALUES (9, 8, 33, 1, 43);
INSERT INTO `goals` VALUES (10, 9, 38, 1, 43);
INSERT INTO `goals` VALUES (11, 9, 20, 1, 53);
INSERT INTO `goals` VALUES (12, 10, 20, 1, 87);
INSERT INTO `goals` VALUES (13, 11, 6, 1, 89);
INSERT INTO `goals` VALUES (14, 11, 15, 1, 68);
INSERT INTO `goals` VALUES (15, 13, 6, 1, 90);
INSERT INTO `goals` VALUES (16, 13, 49, 1, 8);
INSERT INTO `goals` VALUES (17, 15, 55, 1, 68);
INSERT INTO `goals` VALUES (18, 16, 6, 2, 42);
INSERT INTO `goals` VALUES (19, 17, 8, 1, 45);
INSERT INTO `goals` VALUES (20, 18, 6, 2, 90);
INSERT INTO `goals` VALUES (21, 18, 8, 1, 66);
INSERT INTO `goals` VALUES (22, 18, 2, 3, 81);
INSERT INTO `goals` VALUES (23, 18, 53, 1, 31);
INSERT INTO `goals` VALUES (24, 19, 30, 1, 37);
INSERT INTO `goals` VALUES (25, 19, 36, 1, 54);
INSERT INTO `goals` VALUES (26, 20, 6, 1, 38);
INSERT INTO `goals` VALUES (27, 20, 55, 1, 63);
INSERT INTO `goals` VALUES (28, 21, 6, 1, 81);
INSERT INTO `goals` VALUES (29, 21, 46, 1, 90);
INSERT INTO `goals` VALUES (30, 21, 47, 1, 44);
INSERT INTO `goals` VALUES (31, 22, 36, 1, 87);
INSERT INTO `goals` VALUES (32, 22, 36, 1, 88);
INSERT INTO `goals` VALUES (33, 23, 6, 1, 74);
INSERT INTO `goals` VALUES (34, 25, 14, 1, 42);
INSERT INTO `goals` VALUES (35, 26, 6, 1, 90);
INSERT INTO `goals` VALUES (36, 26, 46, 1, 52);
INSERT INTO `goals` VALUES (37, 26, 38, 1, 25);
INSERT INTO `goals` VALUES (38, 27, 33, 1, 6);
INSERT INTO `goals` VALUES (39, 29, 6, 1, 6);
INSERT INTO `goals` VALUES (40, 29, 36, 1, 25);
INSERT INTO `goals` VALUES (41, 30, 53, 1, 70);
INSERT INTO `goals` VALUES (42, 32, 6, 1, 88);
INSERT INTO `goals` VALUES (43, 33, 19, 1, 3);
INSERT INTO `goals` VALUES (44, 36, 17, 1, 68);
INSERT INTO `goals` VALUES (45, 36, 19, 1, 11);
INSERT INTO `goals` VALUES (46, 37, 81, 2, 48);
INSERT INTO `goals` VALUES (47, 37, 17, 1, 90);
INSERT INTO `goals` VALUES (48, 38, 17, 1, 39);
INSERT INTO `goals` VALUES (49, 39, 19, 1, 73);
INSERT INTO `goals` VALUES (50, 40, 30, 1, 65);
INSERT INTO `goals` VALUES (51, 41, 17, 1, 28);

CREATE TABLE `players` (
  `player_id` int(11) NOT NULL auto_increment,
  `surname` varchar(255) default NULL,
  `firstname` varchar(255) default NULL,
  `date_of_birth` date default NULL,
  `position` int(11) default NULL,
  `image` varchar(255) default NULL,
  `date_joined` date default NULL,
  `date_left` date default NULL,
  `previous_clubs` varchar(255) default NULL,
  `place_of_birth` varchar(255) default NULL,
  `home_sponsor` varchar(255) default NULL,
  `away_sponsor` varchar(255) default NULL,
  `profile` longtext,
  `Triallist` varchar(10) default NULL,
  PRIMARY KEY  (`player_id`)
) TYPE=MyISAM AUTO_INCREMENT=103 ;

INSERT INTO `players` VALUES (66, 'Robinson', 'Stuart', '1901-01-01', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'defqwerqwrqwe\r\nqwerqwe\r\nrwer\r\nqwer\r\nqwer\r\nwer\r\nwqer\r\nwqe\r\nrqw\r\nrqw\r\ner\r\nqwer\r\nqwe', NULL);
INSERT INTO `players` VALUES (61, 'Mullin', 'Pat', '1982-05-28', 1, NULL, '2008-06-01', '2009-10-08', NULL, NULL, NULL, NULL, 'Pat signed from Maidstone United, where he had made over 100 appearances, during the summer of 2008 for his second spell with the club. Initially took over the number one shirt from Scott Chalmers-Stevens but injury saw him sidelined until November. Upon his return he was ever-present for the remainder of the season.\r\n\r\nAs a youth Pat was at Coventry City and Millwall and has also featured for Dover Athletic, Sittingbourne and Herne Bay.\r\n\r\nStruggled to gain a first team spot this season as a plethora of goalkeepers came and went and joined Ramsgate in October.', NULL);
INSERT INTO `players` VALUES (5, 'Beresford', 'Marc', '1986-10-12', 1, '', '2008-09-01', '2010-03-01', '', '', '', '', 'Stepped up from local football at the start of September last season to cover a long-term injury to Pat Mullin. Despite featuring on the bench in almost all of last season, he is still to make his first team debut.\r\n\r\nCurrently on a season long loan at Lordswood.', NULL);
INSERT INTO `players` VALUES (40, 'Young', 'Dan', '1988-01-06', 2, NULL, '2007-06-01', NULL, NULL, NULL, NULL, NULL, 'Centre back born in Sidcup who started his career with Derby County. Danny captained the Rams’ youth team before progressing to the reserves, playing regularly for them during 2004/05 and 2005/06.\r\n\r\nAfter being released by Derby Danny had a short spell with Bromley at the start of the 2006/07 season before moving on to Croydon Athletic where he won most of the club’s end of season awards that year.\r\n\r\nHe signed for Margate in the summer of 2007 after turning in some impressive displays during the pre-season friendlies and took over the captains role following Louis Smiths long-term injury last season.', NULL);
INSERT INTO `players` VALUES (59, 'Lewis', 'Ben', '1977-06-22', 2, NULL, '2009-06-01', '2009-11-01', NULL, NULL, NULL, NULL, 'Ben, seemingly, orignally signed for Margate in March 2009 but confusion over his release from Maidstone United prevented this from being finalised until the summer.\r\n\r\nStarted off Heybridge Swifts before joining Colchester United, where he made two youth appearances before joining Southend in August 1997. and scored the winner on his debut against Fulham.\r\n\r\nKnee problems ended his professional career after 14 appearances and 1 goal for the Roots Hall side and he dropped into non-league in 1999 again with Heybridge before being snapped up by home-town club Chelmsford City the following March.\r\n\r\nHe moved to Grays Athletic in May 2002 before moving on to Ford United that December Following this, he has played for non league teams Grays Athletic, Ford United, Chelmsford City, Heybridge Swifts, Welling United, Bishop''s Stortford and Maidstone United\r\n\r\nMoved onto Bishop''s Stortford in November 2004 joining St Albans City in 2006. He made 21 Conference South appearances that season but after just one more start the following term he joined Welling in the Sptember before moving to Maidstone the following May.', NULL);
INSERT INTO `players` VALUES (33, 'Robinson', 'Curtis', '1989-04-22', 2, 'http://www.margate-fc.com/match_gallery/qdig-files/converted-images/Season%202010-11/00.%20Margate%20Squad%20Photos%202010-11/med_IMG_7923.JPG', '2008-06-01', NULL, 'Ramsgate, Dover Athletic', NULL, 'Tom McKenna', NULL, 'Signed for Margate in the summer of 2008 and gradually became a regular and reliable part of the defence with calmness that belies his young age. Curtis also possesses a impressively long throw. <br />\r\n<br />\r\nStarted off with Ramsgate in their Youth and Reserve sides before leaving for Greenwich University and featured for Dover Athletic Reserves before moving to Hartsdown Park.', NULL);
INSERT INTO `players` VALUES (69, 'Wheatley', 'Luke', '1991-04-25', 2, '', '2008-10-01', '2010-01-01', '', '', '', '', 'Local youngster who was called up to the first team squad in October 2008 and scored his first goal two weeks later in the win at Boreham Wood.\r\nAnother of the local lads who have come into the first team with more confidence and strength than you would expect from a defender still in his teens.\r\n\r\nMoved to Ramsgate on an initial one month loan deal in October but returned in mid-November.', NULL);
INSERT INTO `players` VALUES (15, 'Haverson', 'Jack', '1987-08-22', 2, '', '2009-03-01', NULL, '', '', '', '', 'Jack began his career at Ipswich Town, joining their academy at the age of 16. He left the Suffolk side in the summer of 2006 to join AFC Bournemouth but spent much of the following season on loan at Hayes before joining Grays at the start of this season.\r\n\r\nHe joined Bromley in February 2008. Has played also for Sutton United and Sittingbourne.', NULL);
INSERT INTO `players` VALUES (28, 'Morris', 'Kieran', '1987-04-29', 3, '', '2007-08-01', NULL, '', '', '', '', 'Signed for Margate in August 2007 after solid displays in pre-season friendlies. After 22 starts and 15 substitute appearances he was one of the few players who remained with the club under new management for the 2008/09 season.\r\n\r\nKieran started the first ten games but then found himself frequently on the sub''s bench until a surprising appearance at right-back in November saw a new side to the midfielder as he slotted into the position comfortably, although injury did, eventually, restrict him to 29 appearances.\r\n\r\nMoved to Whitstable Town on loan in December.', NULL);
INSERT INTO `players` VALUES (22, 'Lacy', 'Aaron', '1981-06-24', 2, 'http://www.margate-fc.com/match_gallery/qdig-files/converted-images/Season%202010-11/00.%20Margate%20Squad%20Photos%202010-11/med_IMG_7918.JPG', '2010-02-01', NULL, 'Gillingham, Lordswood, Chatham Town, Maidstone United', NULL, 'Alan Anstice', NULL, 'Signed for Margate in February 2010 after over 6 years with Maidstone United and immeidiately caught the attention of the fans with his long throws.<br />\r\n<br />\r\nA right-sided defender who started off with Gillingham and has also featured for Lordswood and Chatham Town.', NULL);
INSERT INTO `players` VALUES (38, 'Wilson', 'Wayne', '1985-09-12', 3, 'http://www.margate-fc.com/match_gallery/qdig-files/converted-images/Season%202010-11/00.%20Margate%20Squad%20Photos%202010-11/med_IMG_7919.JPG', '2008-06-01', NULL, 'Sorrento, Charlton Athletic, Bishop’s Stortford, Stevenage Borough, Thurrock, Basingstoke, Bromley', 'Perth, Australia', '1/2 Skips', 'Tony Tipple', 'Australian born midfielder who joined from Blue Square South side Bromley in the summer of 2008.<br />\r\n<br />\r\nOriginally with&nbsp;Sorrento under 18''s, based in his home town of Perth he moved to England to join&nbsp;Charlton Athletic&rsquo;s academy, playing for their youth side, in 2002/03.<br />\r\n<br />\r\nWayne went on the play for Bishop&rsquo;s Stortford, Stevenage Borough, Thurrock and Basingstoke. <br />\r\n<br />\r\nPlayed in all bar two of Margate''s Ryman Premier games in his first season, finished the season as second top scorer and won the Supporters Player of Year but saw his second season hit by injuries. <br />\r\n<br />\r\nTook temporary charge of the side, with John Keister, for two matches after Mark Butlers resignation in February 2010.', NULL);
INSERT INTO `players` VALUES (80, 'Axon', 'Paul', '1989-05-10', 4, '', '2007-08-01', '2009-11-01', '', '', '', '', 'Paul originally signed for the Blues August 2007 after solid displays in pre-season.\r\n\r\nHaving made 11 appearances for the first team in that season, the nineteen year old was re-signed by new manager Barry Ashby in June 2008 to provide more attacking options but only made three substitute appearances during the season.\r\n\r\nIn 2009/10 Paul just made the bench twice before moving on to Whitstable Town.', NULL);
INSERT INTO `players` VALUES (8, 'Cliff', 'Sam', '1992-03-21', 4, '', '2008-07-01', NULL, '', '', '', '', 'Sam is a centre forward who has impressed the manager during the 2008/09 pre-season and won himself a place in the first team squad.\r\n\r\nOpportunities were few and far between and he had to wait until January for his only appearance, as a substitute.\r\n\r\nHas featured in this summers friendlies and has impressed with his pace.', NULL);

CREATE TABLE `appearances` (
  `app_id` int(11) NOT NULL auto_increment,
  `match` int(11) default NULL,      <--------FK to games.match_id
  `number` int(11) default NULL,
  `player` int(11) default NULL,      <--------FK to players.player_id
  `type` int(11) default NULL,
  `on` int(11) default NULL,
  `off` int(11) default NULL,
  `yellows` int(11) default NULL,
  `red` char(1) default NULL,
  `replaced` int(11) default NULL,
  PRIMARY KEY  (`app_id`)
) TYPE=MyISAM AUTO_INCREMENT=1759 ;


INSERT INTO `appearances` VALUES (1, 1, 1, 66, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (2, 1, 2, 28, 1, 0, 90, 1, 'N', NULL);
INSERT INTO `appearances` VALUES (3, 1, 3, 33, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (4, 1, 4, 59, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (5, 1, 5, 69, 1, 0, 46, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (6, 1, 6, 15, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (7, 1, 7, 22, 1, 0, 31, 0, 'Y', NULL);
INSERT INTO `appearances` VALUES (8, 1, 8, 38, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (9, 1, 9, 53, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (10, 1, 10, 6, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (11, 1, 11, 8, 1, 0, 82, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (12, 1, 12, 80, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (13, 1, 14, 67, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (14, 1, 15, 61, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (15, 1, 16, 63, 2, 46, 90, 0, 'N', 5);
INSERT INTO `appearances` VALUES (16, 1, 17, 25, 2, 82, 90, 0, 'N', 11);
INSERT INTO `appearances` VALUES (17, 2, 1, 61, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (18, 2, 2, 28, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (19, 2, 3, 33, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (20, 2, 4, 59, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (21, 2, 5, 69, 1, 0, 83, 1, 'N', NULL);
INSERT INTO `appearances` VALUES (22, 2, 6, 15, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (23, 2, 7, 44, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (24, 2, 8, 38, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (25, 2, 9, 53, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (26, 2, 10, 6, 1, 0, 90, 1, 'N', NULL);
INSERT INTO `appearances` VALUES (27, 2, 11, 8, 1, 0, 73, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (28, 2, 12, 80, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (29, 2, 14, 22, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (30, 2, 15, 5, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (31, 2, 16, 63, 2, 83, 90, 0, 'N', 5);
INSERT INTO `appearances` VALUES (32, 2, 17, 35, 2, 73, 90, 0, 'N', 11);
INSERT INTO `appearances` VALUES (33, 3, 1, 61, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (34, 3, 2, 35, 1, 0, 76, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (35, 3, 3, 33, 1, 0, 56, 1, 'N', NULL);
INSERT INTO `appearances` VALUES (36, 3, 4, 59, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (37, 3, 5, 15, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (38, 3, 6, 44, 1, 0, 90, 1, 'N', NULL);
INSERT INTO `appearances` VALUES (39, 3, 7, 22, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (40, 3, 8, 38, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (41, 3, 9, 53, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (42, 3, 10, 6, 1, 0, 90, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (43, 3, 11, 68, 1, 0, 46, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (44, 3, 12, 8, 2, 56, 90, 0, 'N', 3);
INSERT INTO `appearances` VALUES (45, 3, 14, 48, 2, 46, 90, 1, 'N', 11);
INSERT INTO `appearances` VALUES (46, 3, 15, 69, 2, 76, 90, 0, 'N', 2);
INSERT INTO `appearances` VALUES (47, 3, 16, 5, 3, 0, 0, 0, 'N', NULL);
INSERT INTO `appearances` VALUES (48, 3, 17, 63, 3, 0, 0, 0, 'N', NULL);

mysql

2 Contributors
9 Replies
302 Views
1 Week Discussion Span
Latest Post 13 Years Ago Latest Post by smantscheff

smantscheff 265 Veteran Poster

13 Years Ago

An interesting problem. I cannot get it to work, though, because MySQL keeps telling me
"ERROR 1054 : Unknown column 'scorer' in 'on clause"
Does anyone else have this problem, too? And maybe a solution?
And kudos for Steve for supplying a complete test case as a starting point.

smantscheff 265 Veteran Poster

13 Years Ago

Intermedia result: switch the table sequence in your join clauses from

FROM goals, games INNER JOIN players

to

FROM games, goals INNER JOIN players

and likewise

FROM appearances, games INNER JOIN players

to

FROM games, appearances INNER JOIN players

to get the query to run (on mysql 5.1.41-3ubuntu12.6).
But the queries have empty results. The players table is missing in your dump file. So I have to take back my "kudos" - test case not complete.
You could create views from both queries and join them on the player_id - or couldn't you?

Edited 13 Years Ago by smantscheff because: n/a

smantscheff 265 Veteran Poster

13 Years Ago

Try this:

create view view1 as 
SELECT *,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 1 THEN 1 ELSE NULL END) AS lgest,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 2 THEN 1 ELSE NULL END) AS lgesub,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 3 THEN 1 ELSE NULL END) AS lgebench,
COUNT(CASE WHEN games.competition = 2 AND appearances.type = 1 THEN 1 ELSE NULL END) AS facst,
COUNT(CASE WHEN games.competition = 2 AND appearances.type = 2 THEN 1 ELSE NULL END) AS facsub,
COUNT(CASE WHEN games.competition = 2 AND appearances.type = 3 THEN 1 ELSE NULL END) AS facbench,
COUNT(CASE WHEN games.competition = 3 AND appearances.type = 1 THEN 1 ELSE NULL END) AS fatst,
COUNT(CASE WHEN games.competition = 3 AND appearances.type = 2 THEN 1 ELSE NULL END) AS fatsub,
COUNT(CASE WHEN games.competition = 3 AND appearances.type = 3 THEN 1 ELSE NULL END) AS fatbench,
COUNT(CASE WHEN games.competition = 4 AND appearances.type = 1 THEN 1 ELSE NULL END) AS kscst,
COUNT(CASE WHEN games.competition = 4 AND appearances.type = 2 THEN 1 ELSE NULL END) AS kscsub,
COUNT(CASE WHEN games.competition = 4 AND appearances.type = 3 THEN 1 ELSE NULL END) AS kscbench,
COUNT(CASE WHEN games.competition = 5 AND appearances.type = 1 THEN 1 ELSE NULL END) AS lcst,
COUNT(CASE WHEN games.competition = 5 AND appearances.type = 2 THEN 1 ELSE NULL END) AS lcsub,
COUNT(CASE WHEN games.competition = 5 AND appearances.type = 3 THEN 1 ELSE NULL END) AS lcbench,
COUNT(CASE WHEN appearances.type = 1 THEN 1 ELSE NULL END) AS tst,
COUNT(CASE WHEN appearances.type = 2 THEN 1 ELSE NULL END) AS tsub,
COUNT(CASE WHEN appearances.type = 3 THEN 1 ELSE NULL END) AS tbench,
SUM(CASE WHEN games.competition = 1 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 1 THEN appearances.on ELSE NULL END) AS lgemins,
SUM(CASE WHEN games.competition = 2 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 2 THEN appearances.on ELSE NULL END) AS facmins,
SUM(CASE WHEN games.competition = 3 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 3 THEN appearances.on ELSE NULL END) AS fatmins,
SUM(CASE WHEN games.competition = 4 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 4 THEN appearances.on ELSE NULL END) AS kscmins,
SUM(CASE WHEN games.competition = 5 THEN appearances.off ELSE NULL END) - SUM(CASE WHEN games.competition = 5 THEN appearances.on ELSE NULL END) AS lcmins,
SUM(appearances.off) - SUM(appearances.on) AS tmins
FROM games, appearances
INNER JOIN players ON appearances.player = players.player_id
WHERE appearances.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6 AND players.player_id <> 1
GROUP BY players.player_id
;
create view view2 as 
SELECT *,
COUNT(CASE games.competition WHEN 1 THEN 1 ELSE NULL END) AS lgegls,
COUNT(CASE games.competition WHEN 2 THEN 1 ELSE NULL END) AS facgls,
COUNT(CASE games.competition WHEN 3 THEN 1 ELSE NULL END) AS fatgls,
COUNT(CASE games.competition WHEN 4 THEN 1 ELSE NULL END) AS kscgls,
COUNT(CASE games.competition WHEN 5 THEN 1 ELSE NULL END) AS lgecgls,
COUNT(goals.goal_id) AS tgls
FROM games, goals
INNER JOIN players ON goals.scorer = players.player_id
WHERE goals.match = games.match_id AND games.date BETWEEN '2010-07-01' AND '2011-06-31' AND games.competition <> 6
GROUP BY players.player_id
;
select * from view1, view2 where view1.player_id = view2.player_id;

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

MargateSteve 12 Light Poster · Answer 1 · 2011-02-16T01:04:15+00:00

That is very puzzling as I have just tried it again myself and it runs with no errors!

If it helps, I do have both of these running online. The first one is GOALS QUERY and the second one, although a bit of a mess as it is only a test page, is APPEARANCES QUERY. What I hope to do is to merge elements from both those pages, plus aggregates between the data on both pages in one output. I will also want to use the same sort of thing on the individual player pages, an example of which is SHAUN WELFORD (the Table Header in that page has 'FULL' instead of 'STARTS' but it relates to the same data).
I have also put a full dump of the tables at TABLES DUMP as the ones I posted before might not have enough related records for the dates in the query.

Thanks for looking into this.
Steve

PS. Any idea how long it took me to realise that anything between code-tags capitalises reserved words (and 'round' for some reason) and that you can check you did put everything in the right case just by clicking 'Toggle Plain Text'????

MargateSteve 12 Light Poster · Answer 2 · 2011-02-16T20:20:31+00:00

Oops! That will teach me to post stuff late at night! I put the sample players table data in the original post but forgot it in the dump. The full players table is PLAYERS TABLE.

I will try the suggestions when I get home from work. I think you may be onto something with the VIEWS suggestion, although I have not really delved into that yet.

Thanks again
Steve

MargateSteve 12 Light Poster · Answer 3 · 2011-02-21T06:06:27+00:00

Firstly, apologies for not getting back sooner but I had to go to Scotland for a few days.

The VIEWS approach is so nearly there but there is just one little thing I cannot solve with it.

Using the code you posted, everything worked fine other than it would only show players who had scored goals (view2). It is pulling the right information for those players from view1 but it was not showing the players who did not appear in view2. The result can be seen HERE

I had a little play around and the closest I got to getting it right is

select * from view1 LEFT JOIN view2 ON (view1.player_id = view2.player_id)

This gives the right records for every player, but it only shows the names of the players in view2. An example is HERE. For those players who only appear in view1 it just shows the comma generated in the html

<td style="text-align:left"><a href="/content/1st_team/squad_details.php?recordID='.$row_gls['player_id']. '">'. $row_gls['surname']. ', '. $row_gls['firstname']. '</a></td>

and does not pass the player_id into the link.

I have tried a few variations on the joins but this is the closest I have come to getting it right. It may just be tired eyes making me miss something obvious so will have another play after work.

Thank you for getting me this close!

Steve

smantscheff 265 Veteran Poster · Answer 4 · 2011-02-21T14:15:52+00:00

The problem is that both views contain the same column names for the same tables.
While the mysql command line interface handles this quite gracefully, the PHP interface identifies columns by column names only, without the table prefix. Therefore the player.* columns from view1 are merged with or overwritten by the player.* columns of view2.
This query illustrates the problem:

select view1.surname,view2.surname from view1 left join view2 on view1.player_id=view2.player_id;

You can overcome this problem by explicity assigning alias names to the columns which you want to pull.

select view1.player_id as theRealId, view1.surname as theRealSurname, view1.*, view2.* from view1 left join view2 on view1.player_id=view2.player_id;

Now you can refer to column theRealId and theRealSurname in your PHP query processing.

MargateSteve 12 Light Poster · Answer 5 · 2011-02-22T05:05:27+00:00

Absolutely perfect!

I tried one aggregate to see if it worked

SELECT view1.player_id as theRealId, view1.surname as theRealSurname, view1.firstname as theRealFirstname, [B]view1.lgemins/view2.lgegls as lminpg[/B], view1.*, view2.* FROM view1 LEFT JOIN view2 on view1.player_id=view2.player_id

and it works absolutely perfectly.

Immense thanks for the help. Not only has that sorted this problem, but now I have a better understanding of how views work, this opens a whole new set of possibilities within the site.

Thanks again
Steve

smantscheff 265 Veteran Poster · Answer 6 · 2011-02-22T15:12:11+00:00

I'm glad to have been of help.
Yes, views can simplify complicated queries a lot. One drawback in MySQL is that they are stored in an very explicit form with all table prefixes and each column listed separately. Have a look at SHOW CREATE VIEW view1. If you add a column to one of the tables of the view, it won't show up in the view until you explicitly insert it. Therefore I find it good practice to store source code for views externally outside of MySQL for further editing.