mySQL help needed: Problem with a LEFT JOIN

**Skunk** · May 27, 2001 02:55

I'm currently working on a PHP/mySQL news system, which I intend to release in the near future as 'ssNews' (to complement my links script ssLinks, while also adding a multiple user permissions system to the ssLinks script). Everything is going pretty well, but I'm having problems with the news comment system.

Visitors to the site can post comments on news articles, which will then be displayed on a seperate page for that news story. The problem I am having is fetching the "number of comments" for each news article from the database.

Here's a simplified run down of the two database tables in question:

Code:

sscomments - the comments added by site visitors
==========
com_id  int(11)
com_itemid  int(11)	ID of news story the comment is attached to
com_nick  varchar(20)	Name of comment author
com_email  varchar(50)	E-Mail address of comment author
com_text  text		Comment text
com_time  int(11)	date/time comment was posted

ssnews - the actual news stories
======
news_id  int(11)
news_user  int(11)	ID of user that posted the comment
news_timestamp  int(11)	date/time article was created
news_headline  varchar(255) news headline
news_text  text		Text of news story

ssusers - the users who can post news on the site
=======
user_id  int(11)
user_nick  varchar(20)	Name of user
user_email varchar(255)	user's e-mail address

Here's the basic SQL statement I use to get all of the information I need:

Code:

SELECT ssnews.*, user_id, user_nick, user_email
FROM ssnews, ssusers,
AND news_user = user_id
ORDER BY news_timestamp DESC

So far, so simple. The problem is that I need to fetch the number of comments listed for that news story as well. Here's what I've tried:

Code:

SELECT ssnews.*, COUNT(sscomments.com_id) AS num_comments
FROM ssnews LEFT JOIN sscomments
ON com_itemid = ssnews.news_id
GROUP BY com_itemid

The first problem with this is it doesn't get the data from the user table as well - I'm not sure how to combine a standard join with a left join in the same query. The second problem is that for some reason it doesn't return every news story in the database. Instead it returns all news stories that have at least one comment attached to them and the first news story (news_id = 1) with num_comments displayed as 0. It should be returning ALL of the news stories with num_comments displayed as 0 on any stories with no comments yet.

If anyone can help me sort this out I'd be most grateful

Cheers,

Skunk

**Alarion** · May 27, 2001 08:15

Hmm, I experimented with this and it seems that your group by clause is wrong.

try this SQL:

Code:

SELECT ssnews.*, count(sscomments.com_id) AS num_comments
FROM ssnews left join sscomments
on sscomments.com_itemid = ssnews.news_id
group by ssnews.news_id

You want to group by the news items, in the news table, not the comments table.

As well, here is the MYSQL output from that query:

Code:

mysql> SELECT ssnews.*, count(sscomments.com_id) AS num_comments
    -> FROM ssnews left join sscomments
    -> on sscomments.com_itemid = ssnews.news_id
    -> group by ssnews.news_id;
+---------+-----------+----------------+-------------------+----------------+--------------+
| news_id | news_user | news_timestamp | new_headline      | news_text      | num_comments |
+---------+-----------+----------------+-------------------+----------------+--------------+
|       1 |         1 |       20010527 | Test Number One   | This is a test |        0 |
|       2 |         1 |       20010527 | Test Number Two   | This is a test |        2 |
|       3 |         1 |       20010527 | Test Number Three | This is a test |        1 |
+---------+-----------+----------------+-------------------+----------------+--------------+
3 rows in set (0.00 sec)