Using AND?OR in a MySQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Using AND?OR in a MySQL Query

Post 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!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Using AND?OR in a MySQL Query

Post 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
LiveFree
Forum Contributor
Posts: 258
Joined: Tue Dec 06, 2005 5:34 pm
Location: W-Town

Post 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!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Post Reply