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,

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
Subject Author Views Date
Thread sql statements... joet 2204 Feb 7, 2003, 4:19 PM
Thread Re: [joet] sql statements...
yogi 2164 Feb 8, 2003, 3:11 AM
Thread Re: [yogi] sql statements...
joet 2147 Feb 12, 2003, 12:28 PM
Post Re: [joet] sql statements...
Jagerman 2142 Feb 12, 2003, 5:28 PM