November 30, 2007 at 2:46 am
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
November 30, 2007 at 3:53 am
Try running the inner sub-query to verify the results....And also you are using a sub-query executed for each row, which can be avoided by using derived tables...
--Ramesh
November 30, 2007 at 5:04 am
or this might help ...
Move the sub query outside of the CTE definition.
WITH GetAllCategories
AS (SELECT
ROW_NUMBER() OVER (ORDER BY [Name] ASC) AS Row
,Id
,ParentCategoryId
,[Name]
FROM
classifieds_Categories)
SELECT
Row
,Id
,ParentCategoryId
,[Name]
,(SELECT COUNT(*)
FROM classifieds_Ads
WHERE
MemberId=@UserId
AND CategoryId=o.Id
AND AdStatus=100) AS NumActiveAds
FROM
GetAllCategories AS o
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 30, 2007 at 6:50 am
thnks it works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply