using Joins rather than sub queries in SQL Server

Hello Guys I am having following tables relationship with me,

and I am writing following Nested Query in my Stored Procedure

. Select useremail,fullname,city,[state], allowAlerts,allowLetters,aboutMe,avatar,dateregistered,
          (Select COUNT(*) from blog_info where blog_info.userId = @userId)as blogCount,
           (Select count(*) from blog_info where blog_info.isfeatured = 1 AND blog_info.userId = @userId)as featuredCount,
           (Select COUNT(*) from b开发者_运维知识库log_comments where blog_comments.userId = @userId)as commentsCount,
            (Select COUNT(*) from forum where forum.createdby = @userId) as forumCount,
             (Select COUNT(*) from forumresponse where forumresponse.userId = @userId)as responseCount 

          from user_info where _id = @userId.

i want to replace this nested Query with the Query that ues Joins .

Pleaes Help me to achieve this.

Thanks in Advance

---

select
    u.useremail,
    u.fullname,
    u.city,
    u.[state],
    u.allowAlerts,
    u.allowLetters,
    u.aboutMe,
    u.avatar,
    u.dateregistered,
    isnull(ub.blogCount,0) blogCount,
    uf.featuredCount,
    uf2.forumCount,
    ur.responseCount 
from
    user_info u
left outer join 
    (select userid, count(*) blogCount from blog_info group by userid) ub on ub.userid = u._id

(and so on doing a similar join for each subquery you had originally)

where
    u._id = @userId

Sorry for the useless formatting!

Note: the query engine is probably smart enough to use the same execution plan for both queries so you won't necessarily see an improvement

---

Have you tried to paste that query and highlight it in the Query Editor, then clicking the Query Designer toolbar button? Management Studio will open the query within Query Designer, rewriting it in terms of joins.

---

an alternative would be to run all those count sub queries and store in seperate SP variables. Have you tried that to see if its more efficient than multiple sub queries in terms of caching etc?