I feel this should be easy, but I have been having a hell of a time with it.

I want to return a recordset (using T-SQL) that displays certain records in a table, with the last column displaying a count pulled from another table, using a key in its WHERE clause...

Something like:

SELECT
a.articleid,
a.title,
(select count(*) from blog_comments where ???)
FROM blog_articles a
INNER JOIN blog_comments c
ON a.articleid=c.articleid

...but where the third select parameter (select count(*) from blog_comments where ???)
returns the number of rows in the blog_comments table that match the articleid of the the each row in the blog_articles table.

So the resulting recordset would be something like:

10003 Fun with Books 6
10004 Fun with Kites 3
10005 Fun with Bikes 1

The 6,3, and 1 would be the number of rows in the blog_comments table with a matching articleid to 10003,1004, and 10005.

I have tried using cursors, but find that it returns multiple recordsets that aren't going to work in the upper layers of my app. And then there is the performance hit...

If I am not making any sense, please let me know...

Thanks, folks...

Ben

If I understand you correctly, I think this will do it:

Holy over-complicating it, batman!

Thanks a lot, that did it...don't know what I was thinking there.

Ben