Query syntax and Join

[stb74]

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.

 

 

[aschk]

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.

[stb74]

Too early in the morning for this but whats DDL.

[aschk]

Sorry, DDL = data definition language, i.e. the CREATE statement.

[stb74]

-- --------------------------------------------------------

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

 

 

[aschk]

Okey dokey, now what are you looking for?

All players that played in season 2 in match 22998 that were substitutes?

[stb74]

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

 

 

 

 

[stb74]

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.

[stb74]

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.

 

 

[aschk]

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

[stb74]

Excellent easy when you know how.

 

 

[stb74]

I do seem to make things a little harder for myself, particular on my site.

[aschk]

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

[stb74]

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.