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
Cell No: 082 870 3205
- Original Message -
From: Sandeep Hundal <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: [EMAIL PROTECTED]
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
