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

[Select]$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";

I've tried to add it just before

ORDER BY SubSelect.sum_points DESC";

And also before

[Select]WHERE Player.MembershipNo = SubSelect.MembershipNo

However neither option works. Then it occured to me that 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

[Select]$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:

[Select]$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:

[Select]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.

I'd really appreciate some help on this.

Thanks a lot

Shrike

It would be very helpful if you explain what the query does. From my understanding it gets the player with the most points and shows points per venue, and overall points for that player. If I am correct, then you want to find out that information within a given timeframe, in which case each subquery would need to have a WHERE clause

Results[i]n[/i].Date BETWEEN x and y;

PHP_Idiot

Your almost right...sorry for not explaining what it does.
The query looks at all players results and calculates who has 750 points or more and at which venue, if a player has 750 or more at two or more venues it lists only the venue with the highest score. It also shows players name and venues qualified in along with their points total for that venue.

I dont fully understand you comment on applying the timeframe to each query, can it not be filtered by date before it looks at anything else. My thinking here is that, if it excludes all dates in results outside of the parameters set then it would need to search and calculate less data.

If what your suggesting is correct how do I go about adding it in, sorry I am still a total noob at php, would you mind being a bit more specific about where i need to add it?

Also do I need to include the Results.Date in the initial Select part of the statement?

Thanks a lot

Shrike

The thing to remember is that the inner (sub) queries are interpreted before the outer query. So I think you would want

//snip//
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
     [b]AND Results1.Date BETWEEN x AND y[/b]
   GROUP BY Player1.MembershipNo, Venue1.VenueID) SubSelect,
//snip//

PHP_Idiot

Oh I didn't know that!! (kind of like brackets in a mathematical equation I guess!)

Thanks for the advice I'll go and try your suggestions and let you know how i get on :-)

PHP_Idiot

I tried your suggestion, but it returns only the columns headers, no data!

I added Results1.Date after

FROM Player,
 (SELECT Player1.MembershipNo,

to see if that would help but the result is the same.

I even tried adding it to all the sub queries, but each time all I get back is the column headers!

Any other ideas?

Thanks a lot

Shrike

Are you sure that there is actually some data within the dates you are specifying?

PHP_Idiot

Shrike;10920473 wrote:
Are you sure that there is actually some data within the dates you are specifying?

Lol, I understand why your asking this, but yes I am 200% certain that his date range contains data 🙂 (but i just double check to be extra sure!)

As you can see below I extended the date range to make sure everything recorded this year was included:

FROM Player,
 (SELECT Player1.MembershipNo, Results1.Date, 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
	[B]  AND Results1.Date BETWEEN 2009-01-01 AND 2009-12-31[/B]
   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