I have some SQL statements that the 3.3.2 engine does not seem to
handle properly.
The project with which this is associated is keeping track of the
game schedule for a Little League division, and being able to produce
the proper standings at the end of the season. The standings are
first based on the number of wins and ties (these are children
playing on unlighted diamonds, so ties are allowed to stand), and if
two or more teams are tied for points, then head-to-head and other
comparisons are used to assign bonus points to break the ties.
I have changed the names of the teams from the actual names and used
the actual schedule results from one 2005 summer division.
Most of the SQL statements are fine. I have added SQL style comments
to indicate the problem area. Is this a limitation/defect in SQLite,
or do I need to restructure the SQL to make it work properly? I am
puzzled because, while this is ugly SQL, it actually works with MySQL
and SQL Server.
Create Table Teams (
TeamID INT NOT NULL,
TeamName VARCHAR(32) NOT NULL,
PRIMARY KEY(TeamID)
);
INSERT INTO Teams(TeamID, TeamName) VALUES(1, 'Team1');
INSERT INTO Teams(TeamID, TeamName) VALUES(2, 'Team2');
INSERT INTO Teams(TeamID, TeamName) VALUES(3, 'Team3');
INSERT INTO Teams(TeamID, TeamName) VALUES(4, 'Team4');
INSERT INTO Teams(TeamID, TeamName) VALUES(5, 'Team5');
INSERT INTO Teams(TeamID, TeamName) VALUES(6, 'Team6');
INSERT INTO Teams(TeamID, TeamName) VALUES(7, 'Team7');
INSERT INTO Teams(TeamID, TeamName) VALUES(8, 'Team8');
INSERT INTO Teams(TeamID, TeamName) VALUES(9, 'Team9');
INSERT INTO Teams(TeamID, TeamName) VALUES(10, 'Team10');
CREATE TABLE Schedule (
GameNumber INT NOT NULL,
Visitor INT NOT NULL,
VisitorScore INT NULL,
Home INT NOT NULL,
HomeScore INT NULL,
WeekDay VARCHAR(16) NOT NULL,
GameDate DATETIME NOT NULL,
PRIMARY KEY(GameNumber)
);
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(1, 1, 6, 2, 9, 'Mon.',
'2005-07-04 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(2, 3, 6, 4, 1, 'Mon.',
'2005-07-04 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(3, 5, 23, 6, 0, 'Mon.',
'2005-07-04 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(4, 7, 4, 8, 15, 'Mon.',
'2005-07-04 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(5, 6, 0, 1, 10, 'Wed.',
'2005-07-06 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(6, 9, 6, 10, 9, 'Wed.',
'2005-07-06 20:30');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(7, 4, 2, 5, 12, 'Wed.',
'2005-07-06 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(8, 9, 5, 3, 11, 'Thurs.',
'2005-07-07 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(9, 2, 2, 7, 7, 'Thurs.',
'2005-07-07 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(10, 10, 7, 8, 1, 'Thurs.',
'2005-07-07 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(11, 7, 0, 6, 16, 'Mon.',
'2005-07-11 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(12, 5, 16, 9, 4, 'Mon.',
'2005-07-11 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(13, 10, 3, 3, 6, 'Mon.',
'2005-07-11 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(14, 8, 15, 2, 11, 'Mon.',
'2005-07-11 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(15, 1, 1, 4, 1, 'Mon.',
'2005-07-11 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(16, 4, 6, 7, 3, 'Wed.',
'2005-07-13 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(17, 10, 6, 2, 7, 'Wed.',
'2005-07-13 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(18, 3, 1, 5, 1, 'Wed.',
'2005-07-13 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(19, 9, 2, 1, 13, 'Wed.',
'2005-07-13 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(20, 6, 0, 8, 10, 'Thurs.',
'2005-07-14 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(21, 4, 1, 5, 1, 'Thurs.',
'2005-07-14 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(22, 1, 8, 10, 14, 'Thurs.',
'2005-07-14 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(23, 5, NULL, 10, NULL, 'Mon.',
'2005-07-18 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(24, 2, 5, 6, 7, 'Mon.',
'2005-07-18 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(25, 7, 11, 9, 9, 'Mon.',
'2005-07-18 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(26, 8, 1, 4, 1, 'Mon.',
'2005-07-18 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(27, 1, 11, 3, 7, 'Tues.',
'2005-07-19 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(28, 4, 8, 2, 7, 'Wed.',
'2005-07-20 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(29, 10, 13, 6, 7, 'Wed.',
'2005-07-20 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(30, 3, 9, 7, 7, 'Thurs.',
'2005-07-21 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(31, 5, 1, 1, 1, 'Thurs.',
'2005-07-21 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(32, 2, 4, 10, 7, 'Thurs.',
'2005-07-21 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(33, 9, 1, 8, 11, 'Fri.',
'2005-07-22 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(34, 6, 4, 4, 7, 'Mon.',
'2005-07-25 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(35, 7, 1, 5, 1, 'Mon.',
'2005-07-25 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(36, 2, 17, 9, 5, 'Mon.',
'2005-07-25 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(37, 8, 8, 3, 3, 'Tues.',
'2005-07-26 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(38, 9, 7, 6, 0, 'Wed.',
'2005-07-27 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(39, 1, 0, 7, 7, 'Wed.',
'2005-07-27 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(40, 4, 10, 3, 2, 'Thurs.',
'2005-07-28 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(41, 5, 1, 8, 1, 'Thurs.',
'2005-07-28 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(42, 9, 7, 7, 8, 'Mon.',
'2005-08-01 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(43, 8, 7, 1, 0, 'Mon.',
'2005-08-01 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(44, 3, 0, 2, 1, 'Mon.',
'2005-08-01 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(45, 10, 6, 4, 4, 'Mon.',
'2005-08-01 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(46, 6, 1, 5, 1, 'Mon.',
'2005-08-01 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(47, 4, 8, 9, 1, 'Wed.',
'2005-08-03 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(48, 7, 5, 10, 6, 'Wed.',
'2005-08-03 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(49, 2, 1, 5, 1, 'Wed.',
'2005-08-03 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(50, 6, 1, 3, 8, 'Thurs.',
'2005-08-04 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(51, 1, 0, 8, 7, 'Thurs.',
'2005-08-04 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(52, 8, 15, 7, 0, 'Mon.',
'2005-08-08 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(53, 3, 10, 6, 4, 'Mon.',
'2005-08-08 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(54, 5, 1, 4, 1, 'Mon.',
'2005-08-08 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(55, 2, 13, 1, 3, 'Mon.',
'2005-08-08 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(56, 10, 5, 9, 3, 'Mon.',
'2005-08-08 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(57, 7, 8, 2, 15, 'Wed.',
'2005-08-10 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(58, 6, 0, 1, 7, 'Wed.',
'2005-08-10 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(59, 3, 5, 9, 5, 'Wed.',
'2005-08-10 18:00');
INSERT INTO Schedule(GameNumber, Visitor, VisitorScore, Home,
HomeScore, WeekDay, GameDate) VALUES(60, 8, 4, 10, 2, 'Wed.',
'2005-08-10 18:00');
CREATE TABLE Standings (
TeamID INT NOT NULL,
OpponentID INT NOT NULL,
Runs INT NOT NULL,
Wins INT NOT NULL,
Loses INT NOT NULL,
Ties INT NOT NULL,
Bonus INT NOT NULL,
PRIMARY KEY(TeamID, OpponentID)
);
INSERT INTO Standings (TeamID, OpponentID, Runs, Wins, Loses, Ties,
Bonus)
SELECT A.TeamID,
A.OpponentID,
SUM(A.Runs) "Runs",
SUM(A.Wins) "Wins",
SUM(A.Loses) "Loses",
SUM(A.Ties) "Ties",
SUM(A.Bonus) "Bonus"
FROM ( SELECT Visitor "TeamID", Home "OpponentID", VisitorScore
"Runs", 1 "Wins", 0 "Loses", 0 "Ties", 0 "Bonus"
FROM Schedule
WHERE VisitorScore >= 0
AND VisitorScore > HomeScore
UNION
SELECT Home "TeamID", Visitor "OpponentID", HomeScore "Runs", 1
"Wins", 0 "Loses", 0 "Ties", 0 "Bonus"
FROM Schedule
WHERE VisitorScore >= 0
AND VisitorScore < HomeScore
UNION
SELECT Visitor "TeamID", Home "OpponentID", VisitorScore "Runs", 0
"Wins", 1 "Loses", 0 "Ties", 0 "Bonus"
FROM Schedule
WHERE VisitorScore >= 0
AND VisitorScore < HomeScore
UNION
SELECT Home "TeamID", Visitor "OpponentID", HomeScore "Runs", 0
"Wins", 1 "Loses", 0 "Ties", 0 "Bonus"
FROM Schedule
WHERE VisitorScore >= 0
AND VisitorScore > HomeScore
UNION
SELECT Visitor "TeamID", Home "OpponentID", VisitorScore "Runs", 0
"Wins", 0 "Loses", 1 "Ties", 0 "Bonus"
FROM Schedule
WHERE VisitorScore >= 0
AND VisitorScore = HomeScore
) A
GROUP BY A.TeamID, A.OpponentID
ORDER BY TeamID, OpponentID;
SELECT A.TeamID,
B.TeamName,
A.Points
FROM ( SELECT TeamID,
(SUM(Wins) * 30) + (SUM(Ties) * 20) + Sum(Bonus)
"Points"
FROM Standings
GROUP BY TeamID
) A
JOIN Teams B ON A.TeamID = B.TeamID
ORDER BY A.Points;
----> The following statement works in other databases but fails in
SQLite with "SQL error: no such column: X.TeamID"
SELECT X.TeamID,
X.TeamName,
Y.TeamID,
Y.TeamName,
X.Points
FROM ( SELECT A.TeamID,
B.TeamName,
A.Points
FROM ( SELECT TeamID,
(SUM(Wins) * 30) + (SUM(Ties) *
20) + Sum(Bonus) "Points"
FROM Standings
GROUP BY TeamID
) A
JOIN Teams B ON A.TeamID = B.TeamID
) X
JOIN ( SELECT C.TeamID,
D.TeamName,
C.Points
FROM ( SELECT TeamID,
(SUM(Wins) * 30) + (SUM(Ties) *
20) + Sum(Bonus) "Points"
FROM Standings
GROUP BY TeamID
) C
JOIN Teams D ON C.TeamID = D.TeamID
) Y ON X.Points = Y.Points
WHERE X.TeamID <> Y.TeamID
ORDER BY X.TeamName;
----> No surprise, since this uses a similar structure, it also
fails, but only in SQLite.
SELECT A.TeamID,
B.Team1Name "Team Name",
A.OpponentID,
B.Team2Name "Opponent Name",
A.Runs,
A.Wins,
A.Loses,
A.Ties,
A.Bonus
FROM Standings A
JOIN ( SELECT X.TeamID "Team1", X.TeamName "Team1Name", Y.TeamID
"Team2", Y.TeamName "Team2Name"
FROM ( SELECT A.TeamID,
B.TeamName,
A.Points
FROM ( SELECT TeamID,
(SUM(Wins) * 30) + (SUM(Ties)
* 20) + Sum(Bonus) "Points"
FROM Standings
GROUP BY TeamID
) A
JOIN Teams B ON A.TeamID = B.TeamID
) X
JOIN ( SELECT A.TeamID,
B.TeamName,
A.Points
FROM ( SELECT TeamID,
(SUM(Wins) * 30) + (SUM(Ties)
* 20) + Sum(Bonus) "Points"
FROM Standings
GROUP BY TeamID
) A
JOIN Teams B ON A.TeamID = B.TeamID
) Y ON X.Points = Y.Points
WHERE X.TeamID <> Y.TeamID
) B ON A.TeamID = B.Team1
AND A.OpponentID = B.Team2
ORDER BY A.TeamID;
