Re: Super SQL problem! Please advise!

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