About SQL SERVER LEFT JOIN and Group by

Question

I have two tables,Blog table has a FK BlogTagID column point to BlogTag table:

Blog table:

BlogID BlogTagID BlogTitle
  1       2        test1
  2       1        test2
  3       2        test3

BlogTag table:

BlogTagID BlogTagName
  1        JAVA        
  2        .NET        
  3        PHP       

I would like to get the result:

BlogTagName  count
   JAVA         1
   .NET         2
   PHP          0

How to get this?Thank you very much!

Answer 1

try this code

select BlogTagName, count(blogid)
from BlogTag bt
left join Blog b on b.blogtagid = bt.BlogTagID
group by BlogTagName

SQL FIDDLE : http://sqlfiddle.com/#!3/356c5/8/0

Answer 2

You can try this also

SELECT BlogTagName,COUNT(BlogTagID) FROM Blog b JOIN BlogTagID bt WHERE b.BlogTagID=bt.BlogTagID GROUP BY BlogTagID;