On Thu, 6 Nov 2003, CPT John W. Holmes wrote:From: "Peter Beckman" <beckman@xxxxxxxxxx>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
