CODEWITHSUNDEEP
sqlsql-servert-sqljoin
TPR
TPR

Reputation: 2577

sql - left join - count

suppose i have two tables. articles and comments.

when i am selecting columns from articles table, i also want to select the number of comments on the article in the same select statement... (suppose the common field between these two tables is articleid)

how do I do that? I can get it done, but I do not know if my way would be efficient, so i want to learn the right way.

Upvotes: 14

Views: 47333

Answers (5)

wwmbes
wwmbes

Reputation: 304

    -- Working Syntax example from my environment changed to fit this context. 
SELECT a.article
    ,A.articleid
    ,(
        SELECT Count(B.articleid)
        FROM dbo.comment AS B
        WHERE A.articleid = B.articleid
        ) AS comment#
    ,(
        SELECT Count(C.articleid)
        FROM dbo.comments AS C
        WHERE A.articleid = C.articleid
        ) AS comments#
FROM dbo.article AS A;

Upvotes: 3

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196002

This should do it..

SELECT
   article_column_1, article_column_2, count( ct.articleid) as comments
FROM
   article_table at
   LEFT OUTER JOIN comment_table ct ON at.articleid = ct.articleid
GROUP BY 
   article_column_1, article_column_2

Upvotes: 6

JBrooks
JBrooks

Reputation: 10013

This should be more efficient because the group by is only done on the Comment table.

SELECT  
       a.ArticleID, 
       a.Article, 
       isnull(c.Cnt, 0) as Cnt 
FROM Article a 
LEFT JOIN 
    (SELECT c.ArticleID, count(1) Cnt
     FROM Comment c
    GROUP BY c.ArticleID) as c
ON c.ArticleID=a.ArticleID 
ORDER BY 1

Upvotes: 22

JonH
JonH

Reputation: 33153

SELECT 
       a.Article,
       a.ArticleID,
       t.COUNTOFCOMMENTS
FROM
       Article a
LEFT JOIN
       Comment c
ON c.ArticleID=a.ArticleID
LEFT JOIN
(SELECT ArticleID, COUNT(CommentID) AS COUNTOFCOMMENTS FROM Comments GROUP BY ArticleID) t
ON t.ArticleID = a.ArticleID

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332591

Use:

   SELECT a.articleid, 
          COUNT(*) AS num_comments
     FROM ARTICLES a
LEFT JOIN COMMENTS c ON c.articleid = a.articleid
 GROUP BY a.articleid

Whatever columns you want from the ARTICLES table, you'll have to define in the GROUP BY clause because they aren't having an aggregate function performed on them.

Upvotes: 11

Related Questions