view entry
Source code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
DROP TABLE IF EXISTS `games`;
CREATE TABLE `games` (
`id` int(10) NOT NULL auto_increment,
`home` int(10) NOT NULL,
`visitor` int(10) NOT NULL,
`hscore` int(11) NOT NULL,
`vscore` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
DROP TABLE IF EXISTS `teams`;
CREATE TABLE `teams` (
`id` int(10) NOT NULL auto_increment,
`teamname` char(20) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO `games` VALUES (1,1,2,3,2),(2,1,2,5,2),(3,2,1,2,5),(4,2,1,2,6),(5,2,1,3,4);
INSERT INTO `teams` VALUES (1,'St. Pauli'),(2,'Lokomotive Zwickau');
SELECT
teamname,
COUNT(homewins.home) AS homewins,
COUNT(homeloss.home) AS homeloss
FROM teams
LEFT JOIN games homewins
ON homewins.home = teams.id
AND homewins.hscore > homewins.vscore
LEFT JOIN games homeloss
ON homeloss.home = teams.id
AND homeloss.vscore > homeloss.hscore
GROUP BY teams.id;
|
|
|
|
