Thanks. That works great. Do you know if there a way to join a table to itself and/or alias a table? For example,
FROM gforum_Post p1, gforum_Post p2
WHERE p1.post_id=p2.post_root_id AND p2.post_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY)) AND p2.post_root_id > 0
GROUP BY p2.post_root_id
ORDER BY num_new_replies DESC
LIMIT 100;
The problem is that when I do the group by post_root_id, the aggregated columns are not necessarily correct.
One more question sql related question, do you know how to do left joins? Something along the lines of...
SELECT a.fieldX, a.fieldY, b.fieldZ
FROM foo a LEFT JOIN bar b ON a.pk = b.fk
WHERE ...
Doing the following in the perl code does a complete join, which is not what I want.
my $q = $DB->table('foo', 'bar');
Thanks. (This would be much easier if mysql had views ;p )
Joe
--
Christianity.com Forums
Code:
SELECT p1.post_id, p1.post_subject, count(p2.post_id) as num_new_replies FROM gforum_Post p1, gforum_Post p2
WHERE p1.post_id=p2.post_root_id AND p2.post_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY)) AND p2.post_root_id > 0
GROUP BY p2.post_root_id
ORDER BY num_new_replies DESC
LIMIT 100;
The problem is that when I do the group by post_root_id, the aggregated columns are not necessarily correct.
One more question sql related question, do you know how to do left joins? Something along the lines of...
Code:
SELECT a.fieldX, a.fieldY, b.fieldZ
FROM foo a LEFT JOIN bar b ON a.pk = b.fk
WHERE ...
Doing the following in the perl code does a complete join, which is not what I want.
Code:
my $q = $DB->table('foo', 'bar');
Thanks. (This would be much easier if mysql had views ;p )
Joe
--
Christianity.com Forums