Gossamer Forum
Home : Products : Gossamer Forum : Discussion :

Re: [yogi] sql statements...

Quote Reply
Re: [yogi] sql statements... In reply to
Thanks. That works great. Do you know if there a way to join a table to itself and/or alias a table? For example,

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...


SELECT a.fieldX, a.fieldY, b.fieldZ

FROM foo a LEFT JOIN bar b ON a.pk = b.fk


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 )

Christianity.com Forums
Subject Author Views Date
Thread sql statements... joet 2769 Feb 7, 2003, 4:19 PM
Thread Re: [joet] sql statements...
yogi 2706 Feb 8, 2003, 3:11 AM
Thread Re: [yogi] sql statements...
joet 2689 Feb 12, 2003, 12:28 PM
Post Re: [joet] sql statements...
Jagerman 2680 Feb 12, 2003, 5:28 PM