"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]
