Post reply

Table Joins and count(*)

  • February 11, 2009 at 1:22 pm

    #127670

    Hi I have 2 tables as follows:

    table = blogEntry: => | BlogId | title | body | catid | DatePublished | PostedBy|

    table = BlogComments => | id | postersname | CommentBody | BlogId | DateCommented |

    Comments table can have multiple rows for a single BlogID in blogtable. I wanted query a data set from BlogTable along with a count of how many Comments for each blogID in comments table.

    I tried the following select statement but it gives me an error..

    Select title, body, catid, datePublished, PostedBy,

    CountComments As (count(BlogId) from BlogEntry a, BlogComments b where a.BlogId= b.BlogId)

    from BlogEntry

    Any suggestion would be appreciated..

  • February 11, 2009 at 1:36 pm

    #942462

    The format for aliases is Expression AS Alias, and your subquery is both unnecessary and incomplete. So..

    SELECT title,

    body,

    catid,

    datePublished,

    PostedBy,

    count(*) AS CountComments

    FROM BlogEntry a INNER JOIN BlogComments b ON a.BlogId= b.BlogId

    GROUP BY title,

    body,

    catid,

    datePublished,

    PostedBy

    I would suggest in future, if a statement gives an error, post the full and complete error message.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 11, 2009 at 2:03 pm

    #942482

    Hi, Thanks for the response!

    Blogs in the BlogEntry table can have comments in the BlogComments table.

    But in the same time there could be rows in the BlogEntry table where there are

    no comments for them. In this select statement I only get those rows with

    comments and misses those rows without comments. I am looking for a table

    ..something like follows..

    BlogTitle | .... | Comments|

    xxxx 3

    xxxxx 0

    xxxx 5

    Thanks....

  • February 11, 2009 at 2:12 pm

    #942486

    Change the inner join to LEFT Outer and change the count as follows

    CASE WHEN b.BlogId IS NULL THEN 0 ELSE COUNT(*) END AS CountComments

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 11, 2009 at 2:45 pm

    #942510

    Thanks Gail! Works perfect!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply