Re: Query or code?
| From: | CPT John W. Holmes | Date: | Thu, 06 Nov 2003 21:54:36 +0000 |
| Subject: | Re: Query or code? | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-31483@lists.php.net to get a copy of this message | ||
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:
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...