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... > > Athough... > > SQL example: Almost; Here's the hard part: mysql> select logid, type, date, ApplicationID, TeamMemberID, Points from log where applicationid=19933 and teammemberid=63 and type="Promotion" order by date desc; +-------+-----------+---------------------+---------------+--------------+--------+ | logid | type | date | ApplicationID | TeamMemberID | Points | +-------+-----------+---------------------+---------------+--------------+--------+ | 2966 | Promotion | 2003-08-14 17:43:22 | 19933 | 63 | 71 | | 2381 | Promotion | 2003-08-01 13:02:56 | 19933 | 63 | 81 | | 2373 | Promotion | 2003-08-01 12:54:20 | 19933 | 63 | 81 | | 2105 | Promotion | 2003-07-31 15:06:55 | 19933 | 63 | 84 | +-------+-----------+---------------------+---------------+--------------+--------+ 4 rows in set (0.02 sec) mysql> select ApplicationID, TeamMemberID, sum(score) as score from score where Applicationid=19933 and teammemberid=63 group by ApplicationID, TeamMemberID; +---------------+--------------+-------+ | ApplicationID | TeamMemberID | score | +---------------+--------------+-------+ | 19933 | 63 | 96 | +---------------+--------------+-------+ 1 row in set (0.01 sec) I want to see the comparison done with the most recent row of type "Promotion" from the log table: +---------------+--------------+-----------+-------+ | ApplicationID | TeamMemberID | log_score | score | +---------------+--------------+-----------+-------+ | 19933 | 63 | 71 | 96 | +---------------+--------------+-----------+-------+ 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. --------------------------------------------------------------------------- Peter Beckman Director of Internet Initiatives North American Management http://www.nambco.com/ 703.683.0292 x239 beckman@xxxxxxxxxx --------------------------------------------------------------------------- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
