Jump to content

Adding a second table to an existing query

MargateSteve

By MargateSteve
in MySQL Help

 Share

Recommended Posts

MargateSteve Regular Member

MargateSteve

Posted
Posted

I currently have a query that shows goals scored by players in a football team but want to also include data from another table to show the appearances that they have made.

 

The current query is

$gls = mysql_query("SELECT *,
COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls,
COUNT(CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END) AS facgls,
COUNT(CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END) AS fatgls,
COUNT(CASE games.competition WHEN 4 THEN goals.goal_id ELSE NULL END) AS kscgls,
COUNT(CASE games.competition WHEN 5 THEN goals.goal_id 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 goals.scorer ORDER BY tgls DESC, lgegls DESC
");
if (!$gls) {
    die("Query to show fields from table failed");
}

And the tables used, with selected data are

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, ;
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 Sorrento under 18''s, based in his home town of Perth he moved to England to join Charlton Athletic’s academy, playing for their youth side, in 2002/03.<br />\r\n<br />\r\nWayne went on the play for Bishop’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);

 

The table I want to join is

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);

 

I want to retain all of the current COUNT's from the first query but also include some (and some SUM's) from the extra table too.

 

I would need to still have the basic games.competition "CASE games.competition WHEN........" criteria but there will be 3 variations on each for each type of appearance (1-Starts, 2-Sub, 3-Bench) so am looking along the lines of

(CASE WHEN games.competition = 1  AND appearances.type = 1 THEN 1 ELSE NULL END) AS lgestarts,
(CASE WHEN games.competition = 1  AND appearances.type = 2 THEN 1 ELSE NULL END) AS lgesub,
(CASE WHEN games.competition = 1  AND appearances.type = 3 THEN 1 ELSE NULL END) AS lgebench,
(CASE WHEN games.competition = 2  AND appearances.type = 1 THEN 1 ELSE NULL END) AS facstarts,
(CASE WHEN games.competition = 2  AND appearances.type = 2 THEN 1 ELSE NULL END) AS facsub, etc etc

 

I have tried this in a query on its own and it works fine but, despite numerous efforts, I cannot get the two queries to work in one.

 

I have tried several types of joins (some giving some bizarre results!) and briefly tried to suss out a subquery but this was way beyond my scope!

 

My last attempt at trying to just get one row from the extra table was 

$gls = mysql_query("SELECT *,
COUNT(CASE WHEN games.competition = 1 AND appearances.type = 1 THEN 1 ELSE NULL END) AS lgestarts,
COUNT(CASE games.competition WHEN 1 THEN goals.goal_id ELSE NULL END) AS lgegls,
COUNT(CASE games.competition WHEN 2 THEN goals.goal_id ELSE NULL END) AS facgls,
COUNT(CASE games.competition WHEN 3 THEN goals.goal_id ELSE NULL END) AS fatgls,
COUNT(CASE games.competition WHEN 4 THEN goals.goal_id ELSE NULL END) AS kscgls,
COUNT(CASE games.competition WHEN 5 THEN goals.goal_id ELSE NULL END) AS lgecgls,
COUNT(goals.goal_id) AS tgls
FROM appearances, goals, games
INNER JOIN players ON appearances.player = players.player_id
INNER JOIN players ON goals.scorer = players.player_id
WHERE goals.match = games.match_id AND appearances.match = games.match_id
AND 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 ORDER BY tgls DESC, lgegls DESC
");
if (!$gls) {
    die("Query to show fields from table failed");
}

but this just returned "Query to show fields from table failed".

 

I am sure that there is a way to do this but I am all googled out! The database is running on MySQL 4.0 and any suggestion would be gratefully received.

 

Thanks in advance

 

Steve

MargateSteve Regular Member

MargateSteve

Posted
  • Author
Posted

I have some progress thanks to your reply Joel24. Not quite there but closer!!

 

My current query is 

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

 

and this does actually generate some data but I now think the problem is with the grouping.

 

This current query is running at http://www.margate-fc.com/content/1st_team/stats/scorers2.php while the original one which is querying the one table is at http://www.margate-fc.com/content/1st_team/stats/scorers.php.

You can see that the new query seems to be getting the right(ish) results but there is no grouping. I did try to join the 'players' table to itself and group by the player_id in that but this cause everything to have the wrong results and every number on the page was in the hundreds!

 

Steve

MargateSteve Regular Member

MargateSteve

Posted
  • Author
Posted

I am still banging my head against a brick wall with this so thought I would add some more information to see if that will help.

 

I have now created the two seperate queries that I want merged into one and both work exactly the way that I want them to individually.

 

The existing query is

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 the query that I want to merge with this is

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

 

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.

 

Any help, advice, suggestions or complete re-writes would be gratefully received!

 

Steve

MargateSteve Regular Member

MargateSteve

Posted
  • Author
Posted

There are actually two reasons but if there is a way around them without merging the queries I will be happy to go with any suggestions!

 

Firstly, I want to be able to show results from both queries in one HTML table, grouped by the player. As I mentioned before, I can get these to work independently but usually a Player Statistics page will show both appearances and goals ie.

Player

Starts

Sub

Goals

Smith

21

3

10

Secondly, I will want to aggregate parts of one query against the other, for example, I would want to divide a players appearances by his goals to get a 'goals per appearance' field.

 

Steve

MargateSteve Regular Member

MargateSteve

Posted
  • Author
Posted

Is there an easy way to aggregate results from 2 different queries then? There would be a way around showing the records from both queries with a bit of tinkering but I can find no way of aggregating between the two.

 

Steve

MargateSteve Regular Member

MargateSteve

Posted
  • Author
Posted

I have got this sorted now. What I needed to do was divide or multiply results from one query by one in another query.

 

In the end I created a view for each query and queried the two views.

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

 

The aggregate part I was trying to do is view1.lgemins/view2.lgegls as lminpg. I needed to divide the number of minutes that the player has played in query/view 1 by the number of goals the player had scored in query/view 2 to find the number of minutes between each goal.

 

Thanks for the help.

Steve

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.