thanks for the tip steve. i read up on left joins, and got this:
SELECT diary.id, diary.thedate, diary.topic, diary.entry
FROM diary
LEFT JOIN comments
ON diary.id = comments.id;
but it only joins the two tables. What I need is only 1 column from
the second table, counted. Something like this, but that doesn't work
:(
SELECT diary.id, diary.thedate, diary.topic, diary.entry
FROM diary
LEFT JOIN comments.id
ON diary.id = comments.id
COUNT(comments.id)
GROUP BY comments.id;
Any ideas how that could work??
Thanks all
/sunny
> > diary.month='$month' AND diary.id = comments.id GROUP BY
> comments.id
> > ORDER BY diary.id DESC "
--- Steve Werby <[EMAIL PROTECTED]> wrote: > "Sandeep Hundal"
<[EMAIL PROTECTED]> wrote:
> > 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.
>
> Take a look at the LEFT JOIN syntax. It will allow you to return
> all
> records from diary and the records from comments that match based
> on a
> common field between the two tables (in this case "id"). This
> query doesn't
> give you exactly what you want, but it demonstrates LEFT JOIN
> usage. You
> had been doing a STRAIGHT JOIN (using alternate syntax where you
> didn't
> actually use those words).
>
> SELECT diary.entry, comments.id
> FROM diary
> LEFT JOIN comments ON comments.id = diary.id
> WHERE where_clause;
>
> See the MySQL documentation for more details. Hopefully you can
> figure it
> out on your own with a few minutes of research, trial and error.
>
> --
> Steve Werby
> COO
> 24-7 Computer Services, LLC
> Tel: 804.817.2470
> http://www.247computing.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]
__________________________________________________
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]
