Passing Column in Sub Select!!!

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

SQL Server 2005 Forums

Transact-SQL (2005)

Passing Column in Sub Select!!!

Author

Topic

fshuja
Starting Member

7 Posts

Posted - 2007-11-30 : 04:44:42

how can i do like this

With GetAllCategories as (
Select ROW_NUMBER() OVER (Order By [Name] ASC) as
Row, Id, ParentCategoryId, [Name],
(Select Count(*) From classifieds_Ads
Where MemberId=@UserId AND CategoryId=Id
AND AdStatus=100)As NumActiveAds
From
classifieds_Categories
)

but this is not working as NumActiveAds is returning always zero.

Any help?
thnks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-30 : 04:50:57

Something like this:

With GetAllCategories as (
Select ROW_NUMBER() OVER (Order By [Name] ASC) as
cc.Row, cc.Id, cc.ParentCategoryId, cc.[Name],
(Select Count(*) From classifieds_Ads
Where MemberId=@UserId AND CategoryId=cc.Id
AND AdStatus=100)As NumActiveAds
From
classifieds_Categories cc
)

fshuja
Starting Member

7 Posts

Posted - 2007-11-30 : 05:07:42

thanks a lot it works with a little change
With GetAllCategories as (
Select ROW_NUMBER() OVER (Order By [Name] ASC) as
Row, Id, ParentCategoryId, [Name],
(Select Count(*) From classifieds_Ads
Where MemberId=@UserId AND CategoryId=cc.Id
AND AdStatus=100)As NumActiveAds
From
classifieds_Categories cc
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources