SELECT     TabID, TabOrder, PortalID, TabName, IsVisible, ParentId, [Level], CASE WHEN LEFT(LOWER(T .IconFile), 6) = 'fileid' THEN
                          (SELECT     Folder + FileName
                            FROM          dbo.Files
                            WHERE      'fileid=' + CONVERT(varchar, dbo.Files.FileID) = T .IconFile) ELSE T .IconFile END AS IconFile, DisableLink, Title, Description, KeyWords, IsDeleted, 
                      SkinSrc, ContainerSrc, TabPath, StartDate, EndDate, Url, CASE WHEN EXISTS
                          (SELECT     1
                            FROM          dbo.Tabs T2
                            WHERE      T2.ParentId = T .TabId) THEN 'true' ELSE 'false' END AS HasChildren, RefreshInterval, PageHeadText, IsSecure
FROM         dbo.Tabs AS T

quote:

---

Originally posted by visakh16

can you try replacing the subqueries with joins?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

SELECT     T.TabID, T.TabOrder, T.PortalID, T.TabName, T.IsVisible, T.ParentId, T.[Level], T.DisableLink, T.Title, T.Description, T.KeyWords, 
                      T.IsDeleted, T.SkinSrc, T.ContainerSrc, T.TabPath, T.StartDate, T.EndDate, T.Url, T.RefreshInterval, T.PageHeadText, T.IsSecure, dbo.Tabs.ParentId AS Expr1
FROM         dbo.Tabs AS T LEFT OUTER JOIN
                      dbo.Tabs ON T.TabID = dbo.Tabs.ParentId

quote:

---

Originally posted by ldc0618

ok, so I decided to just add the haschildren field to my table and then update it as needed. but I am now having a problem with mu update statement. Shouldn't this work?

UPDATE tabs SET haschildren = CASE WHEN EXISTS
(SELECT 1
FROM dbo.Tabs T2
WHERE T2.ParentId = TabId)
THEN 1
ELSE 0 END
GO

I don't get any error, but it never changes haschildren to 1

Thanks in advance!

---

UPDATE t1
SET t1.haschildren = 1 
FROM tabs t1
CROSS APPLY (SELECT COUNT(1) AS Cnt
             FROM tabs
             WHERE ParentId = t1.TabId)t2
WHERE t2.Cnt > 0