SELECT * FROM `tablename` WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
$query = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue,
SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points
FROM Player,
(SELECT Player1.MembershipNo, Venue1.VenueName, SUM(Position1.Points) AS sum_points, COUNT(Player1.MembershipNo) AS count_results
FROM Player Player1, Results Results1, Position Position1, Venue Venue1
WHERE Player1.MembershipNo = Results1.MembershipNo
AND Results1.Position = Position1.Position
AND Venue1.VenueID = Results1.VenueID
GROUP BY Player1.MembershipNo, Venue1.VenueID) SubSelect,
(SELECT Player3.MembershipNo, SUM(Position3.Points) AS sum_points3
FROM Player Player3, Results Results3, Position Position3
WHERE Player3.MembershipNo = Results3.MembershipNo
AND Results3.Position = Position3.Position
GROUP BY Player3.MembershipNo) SubSelect3
WHERE Player.MembershipNo = SubSelect.MembershipNo
AND SubSelect.sum_points=(SELECT MAX(SubSelect1.sum_points2)
FROM (SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
GROUP BY Player2.MembershipNo, Venue2.VenueID) SubSelect1
WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
AND Player.MembershipNo = SubSelect3.MembershipNo
AND SubSelect.sum_points >= 750
ORDER BY SubSelect.sum_points DESC";
SELECT * FROM `tablename` WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
function mysqldate_to_unix_timestamp($date) {
list($year, $month, $day) = explode('-', $date);
return mktime(0, 0, 0, $month, $day, $year);
}
$query = "SELECT Player.MembershipNo, Results.Date, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue,
SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points
FROM Player,
(SELECT Player1.MembershipNo, Venue1.VenueName, SUM(Position1.Points) AS sum_points, COUNT(Player1.MembershipNo) AS count_results
FROM Player Player1, Results Results1, Position Position1, Venue Venue1
WHERE Player1.MembershipNo = Results1.MembershipNo
AND Results1.Position = Position1.Position
AND Venue1.VenueID = Results1.VenueID
GROUP BY Player1.MembershipNo, Venue1.VenueID) SubSelect,
(SELECT Player3.MembershipNo, SUM(Position3.Points) AS sum_points3
FROM Player Player3, Results Results3, Position Position3
WHERE Player3.MembershipNo = Results3.MembershipNo
AND Results3.Position = Position3.Position
GROUP BY Player3.MembershipNo) SubSelect3
WHERE Player.MembershipNo = SubSelect.MembershipNo
AND SubSelect.sum_points=(SELECT MAX(SubSelect1.sum_points2)
FROM (SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
GROUP BY Player2.MembershipNo, Venue2.VenueID) SubSelect1
WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
AND Player.MembershipNo = SubSelect3.MembershipNo
AND SubSelect.sum_points >= 750
WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
ORDER BY SubSelect.sum_points DESC";
$query = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue,
SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points
FROM Player,
(SELECT Player1.MembershipNo, Venue1.VenueName, SUM(Position1.Points) AS sum_points, COUNT(Player1.MembershipNo) AS count_results
FROM Player Player1, Results Results1, Position Position1, Venue Venue1
WHERE Player1.MembershipNo = Results1.MembershipNo
AND Results1.Position = Position1.Position
AND Venue1.VenueID = Results1.VenueID
GROUP BY Player1.MembershipNo, Venue1.VenueID) SubSelect,
(SELECT Player3.MembershipNo, Results.Date, SUM(Position3.Points) AS sum_points3
FROM Player Player3, Results Results3, Position Position3
WHERE Player3.MembershipNo = Results3.MembershipNo
AND Results3.Position = Position3.Position
WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
GROUP BY Player3.MembershipNo) SubSelect3
WHERE Player.MembershipNo = SubSelect.MembershipNo
AND SubSelect.sum_points=(SELECT MAX(SubSelect1.sum_points2)
FROM (SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
GROUP BY Player2.MembershipNo, Venue2.VenueID) SubSelect1
WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
AND Player.MembershipNo = SubSelect3.MembershipNo
AND SubSelect.sum_points >= 750
WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
ORDER BY SubSelect.sum_points DESC";
OPTION ONE:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
ORDER BY SubSelect.sum_' at line 28
OPTION TWO:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Results.Date BETWEEN '2009-06-05' AND '2009-09-31'
GROUP BY Player3.Mem' at line 15
SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
GROUP BY Player2.MembershipNo, Venue2.VenueID
ONE:
SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2, Results2.Date
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
AND Results2.Date BETWEEN '2009-01-01' AND '2009-12-12'
GROUP BY Player2.MembershipNo, Venue2.VenueID
TWO:
SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue, SubSelect.sum_points AS Venue_Points, SubSelect.count_results AS Venue_Play_Count, SubSelect3.sum_points3 AS Total_Points
FROM Player, (
SELECT Player1.MembershipNo, Venue1.VenueName, SUM( Position1.Points ) AS sum_points, COUNT( Player1.MembershipNo ) AS count_results
FROM Player Player1, Results Results1, Position Position1, Venue Venue1
WHERE Player1.MembershipNo = Results1.MembershipNo
AND Results1.Position = Position1.Position
AND Venue1.VenueID = Results1.VenueID
GROUP BY Player1.MembershipNo, Venue1.VenueID
)SubSelect, (
SELECT Player3.MembershipNo, SUM( Position3.Points ) AS sum_points3
FROM Player Player3, Results Results3, Position Position3
WHERE Player3.MembershipNo = Results3.MembershipNo
AND Results3.Position = Position3.Position
GROUP BY Player3.MembershipNo
)SubSelect3
WHERE Player.MembershipNo = SubSelect.MembershipNo
AND SubSelect.sum_points = (
SELECT MAX( SubSelect1.sum_points2 )
FROM (
SELECT Player2.MembershipNo, Venue2.VenueName, SUM( Position2.Points ) AS sum_points2, Results2.Date
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
AND Results2.Date
BETWEEN '2009-04-01'
AND '2009-07-30'
GROUP BY Player2.MembershipNo, Venue2.VenueID ) SubSelect1
WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo
)
AND Player.MembershipNo = SubSelect3.MembershipNo
AND SubSelect.sum_points >=750
ORDER BY SubSelect.sum_points DESC
DECLARE @Begin_Date DateTime;
DECLARE @End_Date DateTime;
SET @Begin_Date = '2009-06-28';
SET @End_Date = '2009-07-03';
SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue,
SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points
FROM Player,
(SELECT Player1.MembershipNo, Venue1.VenueName, SUM(Position1.Points) AS sum_points, COUNT(Player1.MembershipNo) AS count_results
FROM Player Player1, Results Results1, Position Position1, Venue Venue1
WHERE Player1.MembershipNo = Results1.MembershipNo
AND Results1.Position = Position1.Position
AND Venue1.VenueID = Results1.VenueID
AND Results1.Date between @Begin_Date and @End_Date
GROUP BY Player1.MembershipNo, Venue1.VenueName) SubSelect,
(SELECT Player3.MembershipNo, SUM(Position3.Points) AS sum_points3
FROM Player Player3, Results Results3, Position Position3
WHERE Player3.MembershipNo = Results3.MembershipNo
AND Results3.Position = Position3.Position
AND Results3.Date between @Begin_Date and @End_Date
GROUP BY Player3.MembershipNo) SubSelect3
WHERE Player.MembershipNo = SubSelect.MembershipNo
AND SubSelect.sum_points=(SELECT MAX(SubSelect1.sum_points2)
FROM (SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
AND Results2.Date between @Begin_Date and @End_Date
GROUP BY Player2.MembershipNo, Venue2.VenueName) SubSelect1
WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
AND Player.MembershipNo = SubSelect3.MembershipNo
AND SubSelect.sum_points >= 750
ORDER BY SubSelect.sum_points DESC
Title | # Comments | Views | Activity |
---|---|---|---|
mysql Encryption with PHP | 8 | 44 | |
php variable basic question | 12 | 26 | |
Need Distinct Value from Joined Tables in MSSQL | 5 | 22 | |
Fixed Length SQL Query Question | 3 | 5 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
8 Experts available now in Live!