Re: Super SQL problem! Please advise!

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 <steve@247computing.com> wrote: > "Sandeep Hundal" <sandeep_wde@yahoo.com> 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: 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 __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/