stb74 Posted March 31, 2008 Share Posted March 31, 2008 If I run two query's individually I get the correct results but I need to return the results from one query but can't seem to get them joined correctly. The following will get 11 match players SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_appearances A ON ( P.ID_PLAYER = A.ID_PLAYER AND A.ID_SEASON =2 AND A.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 The following will get the match substitutes SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_substitutes S ON ( P.ID_PLAYER = S.ID_PLAYER AND S.ID_SEASON =2 AND S.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 I just can't seem to work out what the correct terminology is when combining the two, maybe you can't do it. SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_appearances A ON ( P.ID_PLAYER = A.ID_PLAYER AND A.ID_SEASON =2 AND A.ID_MATCH =22998 ) JOIN core_player_substitutes S ON ( P.ID_PLAYER = S.ID_PLAYER AND S.ID_SEASON =2 AND S.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 The Above returns nothing, can someone please point out my blinding mistake, cheers. Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/ Share on other sites More sharing options...
aschk Posted April 1, 2008 Share Posted April 1, 2008 Explain what you want (in english), and provide your DDL, then we'll be able to construct a better query for you. There's nothing wrong with the syntax you have provided, but it's obviously not giving you your expected results. Provide the explanation of what you expect and i'm sure we can assist. Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506279 Share on other sites More sharing options...
stb74 Posted April 1, 2008 Author Share Posted April 1, 2008 Too early in the morning for this but whats DDL. Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506291 Share on other sites More sharing options...
aschk Posted April 1, 2008 Share Posted April 1, 2008 Sorry, DDL = data definition language, i.e. the CREATE statement. Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506298 Share on other sites More sharing options...
stb74 Posted April 1, 2008 Author Share Posted April 1, 2008 -- -------------------------------------------------------- -- -- Table structure for table `core_player_appearances` -- CREATE TABLE `core_player_appearances` ( `ID_APPEARANCE` int(10) unsigned NOT NULL auto_increment, `ID_SEASON` mediumint(10) NOT NULL default '0', `ID_MATCH` int(10) NOT NULL default '0', `ID_TEAM` int(10) NOT NULL default '0', `ID_PLAYER` int(10) NOT NULL default '0', PRIMARY KEY (`ID_APPEARANCE`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=772 ; -- -- Dumping data for table `core_player_appearances` -- INSERT INTO `core_player_appearances` VALUES (761, 2, 23000, 405, 11); INSERT INTO `core_player_appearances` VALUES (762, 2, 23000, 405, 30); INSERT INTO `core_player_appearances` VALUES (763, 2, 23000, 405, 9); INSERT INTO `core_player_appearances` VALUES (764, 2, 23000, 405, 33); INSERT INTO `core_player_appearances` VALUES (765, 2, 23000, 405, 24); INSERT INTO `core_player_appearances` VALUES (766, 2, 23000, 405, 16); INSERT INTO `core_player_appearances` VALUES (767, 2, 23000, 405, 28); INSERT INTO `core_player_appearances` VALUES (768, 2, 23000, 405, 41); INSERT INTO `core_player_appearances` VALUES (769, 2, 23000, 405, 2); INSERT INTO `core_player_appearances` VALUES (770, 2, 23000, 405, 12); INSERT INTO `core_player_appearances` VALUES (771, 2, 23000, 405, 4); -- -------------------------------------------------------- -- -- Table structure for table `core_player_substitutes` -- CREATE TABLE `core_player_substitutes` ( `ID_SUBSTITUTE` int(10) unsigned NOT NULL auto_increment, `ID_SEASON` mediumint(10) NOT NULL default '0', `ID_MATCH` int(10) NOT NULL default '0', `ID_TEAM` int(10) NOT NULL default '0', `ID_PLAYER` int(10) NOT NULL default '0', PRIMARY KEY (`ID_SUBSTITUTE`), KEY `ID_SEASON` (`ID_SEASON`,`ID_MATCH`,`ID_PLAYER`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=147 ; -- -- Dumping data for table `core_player_substitutes` -- INSERT INTO `core_player_substitutes` VALUES (144, 2, 23000, 405, 35); INSERT INTO `core_player_substitutes` VALUES (145, 2, 23000, 405, 39); INSERT INTO `core_player_substitutes` VALUES (146, 2, 23000, 405, ; -- -------------------------------------------------------- -- -- Table structure for table `core_players` -- CREATE TABLE `core_players` ( `ID_PLAYER` int(10) NOT NULL auto_increment, `ID_SEASON` mediumint(10) NOT NULL default '0', `ID_CLUB` int(10) NOT NULL default '0', `firstname` varchar(128) NOT NULL default '', `lastname` varchar(128) NOT NULL default '', `number` tinyint(3) NOT NULL default '0', `position` varchar(10) NOT NULL default '', `profile` text NOT NULL, `image` text NOT NULL, `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ID_PLAYER`), KEY `ID_CLUB` (`ID_CLUB`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=59 ; -- -- Dumping data for table `core_players` -- INSERT INTO `core_players` VALUES (1, 2, 1, 'Stephen', 'Hewitt', 0, 'Goalkeeper', 'Egit', '', '2008-02-26 11:33:30'); INSERT INTO `core_players` VALUES (2, 2, 1, 'Stewart', 'Finn', 0, 'Defender', '', '', '2008-02-09 07:03:38'); INSERT INTO `core_players` VALUES (3, 2, 1, 'Ricky', 'Shuttleworth', 0, 'Defender', '', '', '2008-02-09 07:03:58'); INSERT INTO `core_players` VALUES (4, 2, 1, 'Gavin', 'Fox', 0, 'Defender', '', '', '2008-02-09 07:04:07'); INSERT INTO `core_players` VALUES (5, 2, 1, 'John', 'Hunter', 0, 'Midfielder', '', '', '2008-02-09 07:04:17'); INSERT INTO `core_players` VALUES (6, 2, 1, 'Chris', 'Monahan', 0, 'Midfielder', '', '', '2008-02-09 07:04:46'); INSERT INTO `core_players` VALUES (7, 2, 1, 'Tomas', 'McCann', 0, 'Midfielder', '', '', '2008-02-09 07:05:01'); INSERT INTO `core_players` VALUES (8, 2, 1, 'Eamon', 'McCann', 0, 'Midfielder', '', '', '2008-02-09 07:05:12'); INSERT INTO `core_players` VALUES (9, 2, 1, 'Chris', 'Carberry', 0, 'Forward', '', '', '2008-02-09 07:05:27'); INSERT INTO `core_players` VALUES (10, 2, 1, 'Peter', 'Sharvin', 0, 'Forward', '', '', '2008-02-09 07:05:41'); INSERT INTO `core_players` VALUES (11, 2, 1, 'Chris', 'Beattie', 0, 'Midfielder', '', '', '2008-02-09 07:05:57'); INSERT INTO `core_players` VALUES (12, 2, 1, 'John', 'Finnegan', 0, 'Forward', '', '', '2008-02-09 07:06:18'); INSERT INTO `core_players` VALUES (13, 2, 1, 'Martin', 'McKenna', 0, 'Defender', '', '', '2008-02-09 07:07:37'); INSERT INTO `core_players` VALUES (14, 2, 1, 'Sean', 'Tunney', 0, 'Defender', 'Club Trainer', '', '2008-02-27 05:03:24'); INSERT INTO `core_players` VALUES (15, 2, 1, 'Niall', 'McNulty', 0, 'Midfielder', '', '', '2008-02-09 07:06:55'); INSERT INTO `core_players` VALUES (16, 2, 1, 'Paul', 'Daly', 0, 'Midfielder', '', '', '2008-02-09 07:07:54'); INSERT INTO `core_players` VALUES (17, 2, 1, 'Micheal', 'McCann', 0, 'Forward', '', '', '2008-02-09 07:08:02'); INSERT INTO `core_players` VALUES (18, 1, 1, 'Paul', 'McMullan', 0, 'G', '', '', '2008-02-04 15:12:56'); INSERT INTO `core_players` VALUES (19, 1, 1, 'James', 'Oguz', 0, 'F', '', '', '2008-02-04 15:13:30'); INSERT INTO `core_players` VALUES (20, 1, 1, 'Steven', 'Hollywood', 0, 'F', '', '', '2008-02-04 15:14:09'); INSERT INTO `core_players` VALUES (21, 1, 1, 'Andrew', 'Mitchell', 0, 'M', '', '', '2008-02-04 15:14:52'); INSERT INTO `core_players` VALUES (22, 2, 1, 'Phil', 'McManus', 0, 'D', '', '', '2008-02-04 04:45:19'); INSERT INTO `core_players` VALUES (23, 2, 1, 'Michael', 'Teague', 0, 'M', '', '', '2008-02-04 04:48:32'); INSERT INTO `core_players` VALUES (24, 2, 1, 'Ronan', 'Cullen', 0, 'M', '', '', '2008-02-04 04:50:03'); INSERT INTO `core_players` VALUES (25, 2, 1, 'Ciaran', 'Gorman', 0, 'M', '', '', '2008-02-04 16:50:28'); INSERT INTO `core_players` VALUES (26, 2, 1, 'Marc', 'McGerty', 0, 'D', '', '', '2008-02-04 16:51:02'); INSERT INTO `core_players` VALUES (27, 1, 1, 'Brian', 'Teggart', 0, 'G', '', '', '2008-02-04 16:51:24'); INSERT INTO `core_players` VALUES (28, 2, 1, 'Eamonn', 'Devlin', 0, 'M', '', '', '2008-02-04 16:51:51'); INSERT INTO `core_players` VALUES (29, 1, 1, 'Emmanuel', 'Wilkinson', 0, 'F', '', '', '2008-02-04 16:52:16'); INSERT INTO `core_players` VALUES (30, 2, 1, 'Pablo', 'Berrueco', 0, 'F', '', '', '2008-02-04 16:52:35'); INSERT INTO `core_players` VALUES (31, 2, 1, 'Brian', 'Madden', 0, 'D', '', '', '2008-02-04 16:52:58'); INSERT INTO `core_players` VALUES (32, 2, 1, 'Mark', 'Mullan', 0, 'D', '', '', '2008-02-04 16:53:23'); INSERT INTO `core_players` VALUES (33, 2, 1, 'Mark', 'Coyles', 0, 'D', '', '', '2008-02-04 16:53:48'); INSERT INTO `core_players` VALUES (34, 2, 1, 'Joe', 'McKevitt', 0, 'D', '', '', '2008-02-04 16:54:16'); INSERT INTO `core_players` VALUES (35, 2, 1, 'Peter', 'Johnston', 0, 'F', '', '', '2008-02-04 17:04:28'); INSERT INTO `core_players` VALUES (36, 2, 1, 'Niall', 'McLoughlin', 0, 'M', '', '', '2008-02-04 05:05:07'); INSERT INTO `core_players` VALUES (37, 2, 1, 'Conor', 'O''Neill', 0, 'M', '', '', '2008-02-04 17:05:41'); INSERT INTO `core_players` VALUES (38, 2, 1, 'David', 'Hamilton', 0, 'F', '', '', '2008-02-04 17:06:20'); INSERT INTO `core_players` VALUES (39, 2, 1, 'Joe', 'Kennedy', 0, 'D', '', '', '2008-02-04 17:06:53'); INSERT INTO `core_players` VALUES (40, 1, 1, 'Cormac', 'McGarry', 0, 'M', '', '', '2008-02-04 17:07:43'); INSERT INTO `core_players` VALUES (41, 2, 1, 'David', 'Doherty', 0, 'G', '', '', '2008-02-04 17:08:08'); INSERT INTO `core_players` VALUES (42, 2, 1, 'Conor', 'McSteen', 0, 'F', '', '', '2008-03-16 18:50:07'); INSERT INTO `core_players` VALUES (43, 2, 1, 'Steve', 'Moore', 0, 'D', '', '', '2008-02-04 17:09:31'); INSERT INTO `core_players` VALUES (44, 1, 1, 'Barry', 'Meehan', 0, 'F', '', '', '2008-02-04 17:12:38'); INSERT INTO `core_players` VALUES (45, 2, 7, 'R', 'Galbraith', 0, '', '', '', '2008-02-29 13:10:26'); INSERT INTO `core_players` VALUES (46, 2, 7, 'S', 'Williamson', 0, '', '', '', '2008-02-29 13:10:26'); INSERT INTO `core_players` VALUES (47, 2, 7, 'T', 'Beckett', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (48, 2, 7, 'R', 'McComb', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (49, 2, 7, 'P', 'Magee', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (50, 2, 7, 'D', 'Gallagher', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (51, 2, 7, 'P', 'Murphy', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (52, 2, 7, 'G', 'Beattie', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (53, 2, 7, 'C', 'Rea', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (54, 2, 7, 'A', 'Cardwell', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (55, 2, 7, 'P', 'McKee', 0, '', '', '', '2008-02-29 13:12:31'); INSERT INTO `core_players` VALUES (56, 2, 7, 'Sub', '1', 0, 'Midfielder', '', '', '2008-03-20 14:49:42'); INSERT INTO `core_players` VALUES (57, 2, 7, 'Sub', '2', 0, 'Forward', '', '', '2008-03-20 14:49:57'); INSERT INTO `core_players` VALUES (58, 2, 1, 'test', 'test', 0, 'Defender', '', '', '2008-03-21 21:02:39'); Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506307 Share on other sites More sharing options...
aschk Posted April 1, 2008 Share Posted April 1, 2008 Okey dokey, now what are you looking for? All players that played in season 2 in match 22998 that were substitutes? Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506310 Share on other sites More sharing options...
stb74 Posted April 1, 2008 Author Share Posted April 1, 2008 I am trying to get ID_Player, Firstname and Lastname from the core_players table for the players that have played in a particular match (23000). These players will be in the starting 11 (core_player_appearances) or subs (core_player_substitutes). Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506311 Share on other sites More sharing options...
stb74 Posted April 1, 2008 Author Share Posted April 1, 2008 My first query will get the list of 11 players that started the game, the second query will get the list of substitutes. I am looking to build a query that will give me the one list. Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506314 Share on other sites More sharing options...
stb74 Posted April 1, 2008 Author Share Posted April 1, 2008 To save you some hassle, I have done a compromise but I am not sure whether its the best way to do it. I have kept both queries and then combined them using array_merge. Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506316 Share on other sites More sharing options...
aschk Posted April 1, 2008 Share Posted April 1, 2008 Ok, so you want all the players and all the subs that appeared in the match. Easy SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_appearances A ON ( P.ID_PLAYER = A.ID_PLAYER AND A.ID_SEASON =2 AND A.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 UNION SELECT P.ID_PLAYER, concat( P.firstname, ' ', P.lastname ) FROM core_players P JOIN core_player_substitutes S ON ( P.ID_PLAYER = S.ID_PLAYER AND S.ID_SEASON =2 AND S.ID_MATCH =22998 ) WHERE P.ID_CLUB =1 Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506328 Share on other sites More sharing options...
stb74 Posted April 1, 2008 Author Share Posted April 1, 2008 Excellent easy when you know how. Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506347 Share on other sites More sharing options...
stb74 Posted April 1, 2008 Author Share Posted April 1, 2008 I do seem to make things a little harder for myself, particular on my site. Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-506348 Share on other sites More sharing options...
aschk Posted April 2, 2008 Share Posted April 2, 2008 Heh, yeah I can see how you got to your original solution though. Think about it this way, UNION plonks stuff on the bottom of your grid, whereas a JOIN sticks stuff on the right (or left) of your grid (and then matches up by whatever clause you have given it). So in the case of your substitutes you don't really want to stick them onto the right of your grid, you're more interested in sticking them at the bottom of your grid (like some extra rows). Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-507244 Share on other sites More sharing options...
stb74 Posted April 2, 2008 Author Share Posted April 2, 2008 I always wondered why that was doing it that way now I understand. I am building up quite a large stats site for football teams and I have still a long way to go LOL. ??? Thanks for the help. Quote Link to comment https://forums.phpfreaks.com/topic/98822-query-syntax-and-join/#findComment-507252 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.