March 21, 2005 at 5:09 pm
SELECT CategoryID, CategoryName,
(SELECT COUNT(*)
FROM Categories
WHERE ParentID = c.CategoryID) AS NoOfSub
FROM Categories c
WHERE (ParentID = @CategoryID)
I want to modify the above query so as not to return an extra field ("NoOfSub") but to check if the NoOfSub > 0 then CategoryName + 'there is subs" else just the CategoryName.
I have tried using the Case but it is giving me syntax error near comparison (> 0). Any help will be greatly appreciated.
March 21, 2005 at 5:18 pm
SELECT CategoryID,
CategoryName + Case IsNull(dtSubs.NoOfSub, 0) When 0 Then '' Else ' there are subs' End
FROM Categories c
LEFT JOIN
(
SELECT ParentID, COUNT(*) AS NoOfSub
FROM Categories
GROUP BY ParentID
) dtSubs
ON (dtSubs.ParentID = c.CategoryID)
WHERE (c.ParentID = @CategoryID)
March 21, 2005 at 5:31 pm
Thanks a lot PW. Works perfectly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply