On Thu, 6 Nov 2003, CPT John W. Holmes wrote:
From: "Peter Beckman" <[EMAIL PROTECTED]>
I have this data:

   Table Log: appid   userid points  date    type
   Table Score: appid userid score

I want to verify that the last entry in table log of type "x" is equal to
the sum of the scores in table score for the same appid and userid.

Can I do this in SQL easily?  My problem is selecting the correct (most
recent) row in log in which to match the score.

Basically I want a report of AppID, TeamMemberID, log.points, score.score
that shows where points != score;

Why do you have a score table at all? That's just repeating data when you can always do a SUM query on the "log" table to get the score...

I'd still like an answer to this question. Why is there a need for a separate table with scores?

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 wher
e 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)

---John Holmes...

Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/

php|architect: The Magazine for PHP Professionals – www.phparch.com

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to