problem in a query
-
Hi i have two tables
CREATE TABLE [tbl_UserBlog] (
[UserBlogID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[BlogName] [varchar] (50) NULL ,
[BlogEntry] [text] NULL ,
[Active] [bit] NOT NULL DEFAULT (0),
[PublicFlag] [bit] NOT NULL DEFAULT (0),
) ON [PRIMARY]
GO
CREATE TABLE [tbl_Comments] (
[CommentID] [int] IDENTITY (1, 1) NOT NULL ,
[UserBlogID] [int] NULL ,
[Comment] [text] NULL ,
[UserID] [int] NULL ,
) ON [PRIMARY]
GO
I want to get the values from tbl_userBlog and the count of comments for that blog. I have tried it but it errors on group by.
select tub.*, count(tbc.UserBlogID) as commentcount
From tbl_BlogComments tbc, tbl_userBlog tub where tbc.userblogID
in(select UserBlogID from tbl_userBlog where UserID = 1 and active =1
and publicflag = 1) group by tbc.userblogID , tub.blogname
any help is appreciated.
-
You'll need to do something like this...
select a.*, commentcount
from tbl_UserBlog a
inner join (select UserBlogID, count(*) as commentcount From tbl_Comments group by UserBlogID) b
on a.UserBlogID = b.UserBlogID
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
select tub.userblogID , tub.blogname, count(tbc.UserBlogID) as commentcount
From tbl_BlogComments tbc
INNER JOIN tbl_userBlog tub ON tub.userblogID = tbc.UserBlogID
where tbc.userblogID in(select UserBlogID from tbl_userBlog where UserID = 1 and active =1 and publicflag = 1)
group by tub.userblogID , tub.blogname
Don't ever use * in select.
_____________
Code for TallyGenerator -
Wouldn't the following cause one less access to the table tbl_userBlog?
Also this will ignore userBlog's with no comments
select tub.userblogID , tub.blogname, count(tbc.UserBlogID) as commentcount
From tbl_BlogComments tbc
INNER JOIN tbl_userBlog tub ON tub.userblogID = tbc.UserBlogID
where tub.UserID = 1 and tub.active =1 and tub.publicflag = 1
group by tub.userblogID , tub.blogname
-
There is a condition:
where tbc.userblogID in(select UserBlogID from tbl_userBlog where UserID = 1 and active =1 and publicflag = 1)
This allows only to return BlogNames with existing comments.
So my query does not eliminate any row from returning set.
Of course, original query is not right in many ways. But if they are happy with that...
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply