Can anyone help me sort this sql query by date!

[PHP_Idiot]

I have the below uber query which I have had a lot of help putting it together.

 

However I now need it to sort the results between two dates. I know I need to insert something like this which already works in my other queries:

AND Date
BETWEEN '2009-07-05'
AND '2009-10-04'

 

My Query (below) is very complex and well beyond my understanding unfortunately, I've tried various things, and still can't get it to work, can anyone please tell me where I need to add the above code in this....

$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"; 

 

[dezkit]

Add that just before

ORDER BY SubSelect.sum_points DESC";

[The Eagle]

I beg to differ dezkit, why would you enter it before ordering the script to calculate the points, when it could be able to calculate the date before registering the points.

 

You can try dezkit's version, than try mine.

 

Add  your code before

 

WHERE Player.MembershipNo = SubSelect.MembershipNo

[PHP_Idiot]

Thanks for the advice from you both, unfortunately neither option works, although I think I know why.

 

In the opening statements there is no call to the 'Results' table which is where the field 'Date' is stored.

 

I have tried adding Results.Date to

$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

to make it:

$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

But that didn't work 

 

I also tried adding it after this bit:

FROM Player,
(SELECT Player1.MembershipNo,

 

again no success. I might be barking up the wrong tree (so to speak!) so if I'm way off please let me know.

 

Thanks a lot

[PHP_Idiot]

Any ideas anyone?