Re: Super SQL problem! Please advise!

Sandeep try this SELECT d.id, d.thedate, d.topic, d.entry, count(c.id) AS comments FROM diary d left outer join comments c on (d.id = c.id) WHERE d.month='$month' GROUP BY c.id ORDER BY d.id DESC " Cheers _____________ Graham Reeves Consultant PQ Africa - Q Data Consulting www.pqafrica.co.za Tel No : +27 +43 +722 1111 Cell No: 082 870 3205 ----- Original Message ----- From: Sandeep Hundal <sandeep_wde@yahoo.com> To: <php-general@lists.php.net> Sent: Thursday, February 08, 2001 6:11 AM Subject: [PHP] Super SQL problem! Please advise! > Hey all, > > I got this massive query, which I hope you'll be able to understand: > > SELECT diary.id, diary.thedate, diary.topic, diary.entry, > count(comments.id) AS comments FROM diary, comments WHERE > diary.month='$month' AND diary.id = comments.id GROUP BY comments.id > ORDER BY diary.id DESC " > > Basically what I've done is made a diary table, and allowed people to > comment on each diary entry, with the comments stored in a seperate > comments table. What i'm doing there is getting the diary entry, plus > the number of comments posted for that entry. > > Now the problem is that this query only displays entries from the > diary table when a comment is added for that entry, otherwise it > comes up with a blank, because (diary.id = comments.id) isn't true. > > Now, is there a way to make sure that it displays all diary entries > anyway? because if i remove that check (diary.id = comments.id) - > then all the numbers come up messed up. > > Thanks > Sunny > > __________________________________________________ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail - only $35 > a year! http://personal.mail.yahoo.com/ > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > >