Thanks for the suggestion! I modified your query to something that works for me (I was inaccurate with the column names before, sorry... :)
SELECT a.article_id,a.title,a.date,a.summary,COUNT(c.id) as comments FROM articles as a,article_comments as c WHERE a.article_id = c.article_id GROUP BY a.article_id,a.title,a.date,a.summary ORDER BY a.date DESC LIMIT 3 This works PERFECTLY Except: If an article has NO comments, it is not returned at all! So: When my articles table contains only 3 articles, and I make a query that has LIMIT 3, you would think that this query would return all articles, but it does not: Only the ones that actually has one or more comments. I tried this with 3 articles where 2 of them had comments: Only 2 rows returned. When I gave one comment on the last article: 3 rows returned. Seems like if COUNT(c.id) returns 0, then the row is not returned at all. Suggestions? >* 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 (##)! **ROGER >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
