Trouble with chaining joins

Question

I was wondering if someone could help me with chaining joins - I'm not understanding the thought process.

An example with three tables:

ArticleCategories
-----------------
CategoryID
CategoryName

Articles
---------
ArticleID
ArticleText
CategoryID (FK)

ArticleComments
-----------------
CommentID
ArticleID (FK)
CommentText

I have an sp to get article info for all articles of a particular category, including a count of comments for an article, but I think it needs improvement. My struggle has resulted in this:

---

With resultSet AS
(
  select
    a.ArticleID
  , a.ArticleText
  , a.CategoryID
  , c.CommentCount
    from Articles a
    Left Outer Join
    (Select count(c.CommentID) as CommentCount, c.ArticleID
     from Comments c    
     Group BY c.ArticleID
    ) c
    on a.ArticleID = c.ArticleID 
)
select * from resultSet
where resultSet.CategoryID = 2

---

How should I have written this? I was looking for a way to eliminate the resultSet and the select on the resultSet.

Thanks much for any help Bill

Answer 1

Close ....

You can take the inner select out:

select a.ArticleID , a.ArticleText , a.CategoryID , count(c.CommentCount) 
from Articles a
left join Comments c on a.articleid=c.articleid
where articleid=2
group by a.ArticleID , a.ArticleText , a.CategoryID

Answer 2

select a.articleid, count(c.*) as commentcount
from articles a
left join articlecomments c on a.articleid = c.articleid
where a.categoryid = @categoryid
group by a.articleid

Answer 3

If I'm understanding you right you're just after something like:

SELECT
    a.ArticleID,
    a.ArticleText,
    a.CategoryID,
    (select count(*) from comments c where c.articleid=a.articleid)
FROM
    article a
WHERE
    a.categoryid=2

OR

SELECT
    a.ArticleID,
    a.ArticleText,
    a.CategoryID,
    count(c.commentid)
FROM
    article a
    left outer join comment c on c.articleid=a.articleid
WHERE
    a.categoryid=2
GROUP BY
    a.ArticleID,
    a.ArticleText,
    a.CategoryID

Answer 4

He's the process I go through when building queries:

First, what's the primary driving table? For you, you're getting information about articles, so that suggest's the articles table:

select * from articles

Next, what other tables do I need to pull in, and are these required tables, to get the rest of the information that I need? You need ArticleComments, which is optional:

select
  *
from
  Articles a
  left join ArticleComments acomm on acomm.ArticleID = a.ArticleID

And now, what data do I actually need this query to return? (And also mixed in, just to save a step, what selection criteria do I need?)

select
  a.ArticleID,
  a.ArticleText,
  a.CategoryID,
  count(c.*) CommentCount
from
  Articles a
  left join ArticleComments acomm on acomm.ArticleID = a.ArticleID
where
  a.CategoryID = @CatID
group by
  a.ArticleID

Just break it down, one step at a time, and work through where you need to get data from, and what data you need.

Answer 5

Your query with the WITH clause removed:

   SELECT a.articleid,
          a.articletext,
          a.categoryid,
          COALESCE(c.commentcount, 0) AS commentcount,
     FROM ARTICLES a
LEFT JOIN (SELECT c.articleid,
                  COUNT(c.commentid) AS commentcount
             FROM COMMENTS c    
         GROUP BY c.articleid) c ON c.articleid = a.articleid
   WHERE a.categoryid = 2

I added the COALESCE to handle when an article has no comments - it's safe in both Oracle and SQL Server though you didn't mention which you are using. To my knowledge, it could only be SQL Server, Oracle or DB2 because of the WITH clause.