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... Athough... SQL example: mysql> select * from log; +----+------+ | id | log | +----+------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 3 | | 2 | 5 | +----+------+ 6 rows in set (0.00 sec) mysql> select * from score; +----+-------+ | id | score | +----+-------+ | 1 | 10 | | 2 | 1 | +----+-------+ 2 rows in set (0.00 sec) mysql> select s.id, s.score, sum(l.log) as log_sum from score s, log l where l.id = s.id group by l.id; +----+-------+---------+ | id | score | log_sum | +----+-------+---------+ | 1 | 10 | 10 | | 2 | 1 | 8 | +----+-------+---------+ 2 rows in set (0.00 sec) mysql> select s.id, s.score, sum(l.log) as log_sum from score s, log l where l.id = s.id group by l.id having s.score != log_sum; +----+-------+---------+ | id | score | log_sum | +----+-------+---------+ | 2 | 1 | 8 | +----+-------+---------+ 1 row in set (0.00 sec) ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
