On Thu, 6 Nov 2003, John W. Holmes wrote:
> I'd still like an answer to this question. Why is there a need for a
> separate table with scores?
The log is a "snapshot" in time -- what was the "total points" at the time
of the log entry.
The score table is always the accurate current score.
> > Can it be done with a single query? I can do it brilliantly easily in
> > code, but I like the challenge of doing it in SQL.
>
> Without knowing the exact table structure, maybe this'll work?
>
> mysql> select s.applicationid, s.teammemberid, l.points, sum(s.score),
> date from score s, log l where s.applicationid = l.applicationid group by
> l.points order by l.date desc limit 1;
> +---------------+--------------+--------+--------------+---------------------+
> | applicationid | teammemberid | points | sum(s.score) | date |
> +---------------+--------------+--------+--------------+---------------------+
> | 19933 | 63 | 71 | 96 | 2003-08-14 17:43:22 |
> +---------------+--------------+--------+--------------+---------------------+
> 1 row in set (0.00 sec)
That works for 1 row, but I want 30+ unique appid's and teammemberid's
with the most recent date points. Since some 2nd-to-last "promotions"
occurred much later than the last promotion on others, ordering by date
doesn't help either.
Basically I need a report that says:
When comparing the last Promotion log entry on table log, these are the
applicationid and teammemberid combinations in which the sum of the
items matching in the score table does not equal the points in the
selected log entry.
Peter
---------------------------------------------------------------------------
Peter Beckman Internet Guy
beckman@xxxxxxxxxxxxx http://www.purplecow.com/
---------------------------------------------------------------------------
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
