Modify query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Modify query

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-29 : 01:32:31

Friends,

I have a query for displaying blog and blog comments

select b.*,(select count(id) from prg_blogcmnt where b.blogid=blogid and status=1 and deleted=0) as Comment from prg_blogmgmt b where b.status=1 and b.deleted=0 order by b.creationdatetime desc

This is used to get the all the blog and Number of Comments for the Each Blog.

What i need is when i pass the blog id in the where condition, i am getting value if the Blog Id exist in the prg_blogCmnt table so there is no worries, But if the BlogId does not exist in the prg_blogCmnt that is not returing any value, Because there is no comment for the particular blog in the prg_blogcmnt.

But the expected output is , if i dont have any comment in the prg_blogcmnt for the particular blog , i have to return the comment as 0.

Please help me to modify the query

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-29 : 03:18:05

[code]
select b.*,coalesce(c.commentcnt,0)
from prg_blogmgmt b
left join (select blogid,count(id) as commentcnt
from prg_blogcmnt
where status=1
and deleted=0
group by blogid)c
on b.blogid=c.blogid
where b.status=1
and b.deleted=0
order by b.creationdatetime desc
[/code]

dhinasql
Posting Yak Master

195 Posts

Posted - 2009-03-29 : 03:35:58

Thank you so much visakh

I got the expected output

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-29 : 03:47:04

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources