Hello All,
I have been trying to get a script to come up with the total number of comments a news item has under it
news
-------
id
author
posted
title
text
news_comments
----------
id
nid
poster
posted
comment
And I am using this query:
SELECT n.id as id, n.author as author, n.posted as date, n.title as title, n.text as text, COUNT(c.id) as num_comments FROM news as n, news_comments as c WHERE n.id = c.nid GROUP BY n.id LIMIT 10
Of course that doesnt work because it only shows news entries that have comments, Ive been playing with using 2 queries but I havent found an efficiant way of doing it.
Thanks for all advice!
Using AND?OR in a MySQL Query
Moderator: General Moderators
Re: Using AND?OR in a MySQL Query
(untested)
Code: Select all
SELECT
news.id AS news_id,
COUNT(news_somments.id) AS comments_count
FROM
news
LEFT OUTER JOIN news_comments on news.id = news_comments.nid
GROUP BY
news.news_id
Thanks Tim!
It looks like it will work but Im having trouble adapting it to fit my query:
SELECT n.id as newsID, n.title as newsTitle, n.posted as newsDate, n.author as newsAuthor, n.text as newsText, COUNT(`c.id`) as num_comments FROM ".$prefix."_news as n LEFT OUTER JOIN ".$prefix."_news_comments as c ON n.id = c.nid GROUP BY n.id LIMIT 10
I get: Unknown column 'c.id' in 'field list'
So the join alias is not aplying to the whole query.
Thanks for the help!
It looks like it will work but Im having trouble adapting it to fit my query:
SELECT n.id as newsID, n.title as newsTitle, n.posted as newsDate, n.author as newsAuthor, n.text as newsText, COUNT(`c.id`) as num_comments FROM ".$prefix."_news as n LEFT OUTER JOIN ".$prefix."_news_comments as c ON n.id = c.nid GROUP BY n.id LIMIT 10
I get: Unknown column 'c.id' in 'field list'
So the join alias is not aplying to the whole query.
Thanks for the help!
I just tried it and everyting works just fine...
Code: Select all
SELECT p.ID, COUNT( c.comment_ID )
FROM wp_timvwblogposts AS p
LEFT OUTER JOIN wp_timvwblogcomments AS c ON p.ID = c.comment_post_ID
GROUP BY p.ID