Re: Query or code?
| From: | beckman at purplecow dot com | Date: | Thu, 06 Nov 2003 23:08:10 +0000 |
| Subject: | Re: Query or code? | ||
| References: | 1 2 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-31484@lists.php.net to get a copy of this message | ||
On Thu, 6 Nov 2003, CPT John W. Holmes wrote:
> From: "Peter Beckman" <beckman@nambco.com>
>
> > 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@nambco.com
---------------------------------------------------------------------------