* Torkil Johnsen [...] > I have no problems displaying the title, date, author, summary of the > articles or anything. Its just doing an sql query that gets my 3 latest > articles AND counts how many comments each article has, and doing > it in ONE query instead of two separate ones... :( [...] > One contains articles, basically like this: > ------------------------------------------------------- > | id | title | summary | text | date | author | > ------------------------------------------------------- > > The other containts comments to the articles, basically looks like this: > > ----------------------------------- > | id | article_id | text | author | > ----------------------------------- > > Now, on my main page I want to display the first 3 articles like this: > > TITLETITLETITLETITLETITLE (date) > written by AUTHOR > > SUMMARY SUMMARY SUMMARY SUMMARY SUMMARY SUMMARY > SUMMARY SUMMARY SUMMARY SUMMARY SUMMARY SUMMARY > SUMMARY SUMMARY SUMMARY SUMMARY SUMMARY SUMMARY > >> Read the whole story (comments: ##) > > My actual problem is how to get the number of comments (##)!
Try something like this: SELECT a.title,a.date,a.author,a.summary,COUNT(c.id) as comments FROM articles as a,article_comments as c WHERE a.id = c.article_id GROUP BY a.title,a.date,a.author,a.summary ORDER BY a.date DESC LIMIT 3 -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
