Vote Count 1

[overyde]

Hey,
I have a voting system for images for a competition a client is holding. I have 2 tables: entry and votetrail.
I need to know the statement to be able to count who has the highest votes at any particular date/time in the past.
My tables look like this:

Entry:
--------------------------------------------------
id | Name | ImageLink |Timestamp
--------------------------------------------------
54 | Sum Body |image23.jpg |2010-10-17 10:04:14
58 | Joe Bloggs |image101.jpg |2010-10-19 10:04:14

Votetrail:
----------------------------------------------------
id| EntryId | VoterId | Timestamp
----------------------------------------------------
23| 54 |367 | 2010-10-17 10:10:34
24| 54 |369 | 2010-10-18 13:10:14
25| 58 |375 | 2010-10-19 10:06:18
26| 54 |356 | 2010-10-19 10:10:15

So the result I would like to see for results on or before 2010-10-19 10:30:00 would be:
-----------------------------------------
Entryid | ImageName |EntrantName |Votes|
54 | Image23.jpg | Sum Body | 4 |
58 | Image101.jpg | Joe Bloggs | 1 |
-------------------------------------------

Thanks...



Reality is built on a foundation of dreams.

 

[r937]

SELECT e.id        AS EntryID
     , e.ImageLink AS ImageName
     , e.Name      AS EntrantName
     , COUNT(v.EntryID) AS Votes
  FROM Entry AS e
LEFT OUTER
  JOIN Votetrail AS v
    ON v.EntryID = e.id
   AND v.Timestamp <= '2010-10-19 10:30:00'
GROUP
    BY e.id

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[overyde]

r937...
You're a ***ROCKSTAR***
Thanks!

Reality is built on a foundation of dreams.

 

[ingresman]

Just out of interest, how are you going to prevent multiple votes from the same person ?

 

[overyde]

ingresman...
This is just an "abbreviated" version of the database and tables there is various other fields that have been omitted for simplicity sake. The multiple vote situation has been identified and solved. The database is for a facebook app and one of the fields is the user's unique facebook id which is recorded on the votetrail table.

Reality is built on a foundation of dreams.