Page 1 of 1

Using AND?OR in a MySQL Query

Posted: Sat Nov 04, 2006 12:07 pm
by LiveFree
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!

Re: Using AND?OR in a MySQL Query

Posted: Sat Nov 04, 2006 12:38 pm
by timvw
(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

Posted: Sat Nov 04, 2006 8:06 pm
by LiveFree
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!

Posted: Sat Nov 04, 2006 8:53 pm
by timvw
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