Re: [PHP-DB] Query or code?
From: Peter Beckman [EMAIL PROTECTED] I have this data: Table Log: appid userid points datetype 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
Re: [PHP-DB] Query or code?
On Thu, 6 Nov 2003, CPT John W. Holmes wrote: From: Peter Beckman [EMAIL PROTECTED] I have this data: Table Log: appid userid points datetype 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 Managementhttp://www.nambco.com/ 703.683.0292 x239[EMAIL PROTECTED] --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query or code?
[EMAIL PROTECTED] wrote: On Thu, 6 Nov 2003, CPT John W. Holmes wrote: From: Peter Beckman [EMAIL PROTECTED] I have this data: Table Log: appid userid points datetype 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
Re: [PHP-DB] Query or code?
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 [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
