query help/sugestions

Hello,

I'm developing a small web application that essentialy lets users post articles and then lets other users post comments regarding those articles. This application is already running actualy, i'm working on a new version. The current version runs on PHP+MySQL the new version will be using J2EE with PostgreSQL.

Anyway, i'm trying to solve a problem which I had with my previous version. The problem boils down to how to efficiently display an article in either in its full form or in a article listing page (think front page of slashdot) while at the same time finding out what the comment count is for that article.

I have 2 tables a "content" table which holds the articles, and a "comments" table that you guessed it holds the comments. In the past what I did was to create one query that returned all of the articles (remember I was using MySQL), then while I looped through the results of that query, in PHP I would create another SQL object and issue another query that went out and did a select count(newsid) for each article. This did what I wanted but I always thought it was a bit of a hack and it did not perform well under stress.

Is there a better way to this? I have a few ideas...

I was thinking of using a SQL sub select, but to be honest I can't seem to construct the proper query to give me what I need, which would be a result set that would look something like this:

newsid | user | date | title | intro | commentcount

Another idea I had was to just create a count column in my content table, which gets incremented each time a comment is posted.

Taking the above idea a step forward, i could problably create a trigger to keep that column counter updated whenver a new comment is inserted. Now that I think of it, it problably wouldn't work as the trigger could not be generic.

Anyway, I was just wondering if anyone out here had any ideas or past experience that wouldn't mind sharing.

Thanks a bunch,
John