select tcontentcategories.categoryID,tcontentcategories.name,tcontentcategories.parentID,tcontentcategories.isActive,tcontentcategories.isInterestGroup,tcontentcategories.isOpen, count(tcontentcategories2.parentid) as hasKids ,tcontentcategories.restrictGroups from tcontentcategories left join tcontentcategories tcontentcategories2 ON (tcontentcategories.categoryID = tcontentcategories2.parentID) where tcontentcategories.siteid= and tcontentcategories.parentID = is null and tcontentcategories.name like and tcontentcategories.isActive=1 and tcontentcategories.isInterestGroup=1 group by tcontentcategories.categoryID,tcontentcategories.name,tcontentcategories.parentID,tcontentcategories.isActive,tcontentcategories.isInterestGroup,tcontentcategories.isOpen,tcontentcategories.restrictGroups order by tcontentcategories.name select count(*) as theCount from tcontentcategories where siteid= and isInterestGroup=1 and isactive=1 select count(*) as theCount from tcontentcategories where siteid= and isactive=1 select * from tcontentcategories where siteid= and tcontentcategories.name like order by name select * from tcontentcategories where siteid= and isInterestGroup=1 and tcontentcategories.name like order by name select tcontent.title, tcontent.releasedate, tcontent.menuTitle, tcontent.lastupdate, tcontent.summary, tcontent.filename, tcontent.type, tcontent.contentid, tcontent.target, tcontent.targetParams, tcontent.restricted, tcontent.restrictgroups, tcontent.displaystart, tcontent.displaystop, 0 as comments, tcontentcategoryassign.orderno,tcontent.display,tcontent.active,tcontent.approved,tcontentcategoryassign.featureStart,tcontentcategoryassign.featureStop, tcontentcategoryassign.isFeature,tfiles.fileSize,tfiles.fileExt, tcontent.fileID, tcontent.fileID, tcontent.credits, tcontent.remoteSource, tcontent.remoteURL, tcontent.remoteSourceURL, tcontent.Audience, tcontent.keyPoints from tcontent inner join tcontentcategoryassign ON (tcontent.contenthistid=tcontentcategoryassign.contenthistID) Left Join tfiles ON (tcontent.fileID=tfiles.fileID) where tcontent.active=1 and tcontentcategoryassign.isFeature > 0 and tcontentcategoryassign.categoryID= order by tcontent.#arguments.categoryBean.getSortBy()# #arguments.categoryBean.getSortDirection()# tcontentcategoryassign.#arguments.categoryBean.getSortBy()# #arguments.categoryBean.getSortDirection()# select tcontent.title, tcontent.releasedate, tcontent.menuTitle, tcontent.lastupdate, tcontent.summary, tcontent.filename, tcontent.type, tcontent.contentid, tcontent.target, tcontent.targetParams, tcontent.restricted, tcontent.restrictgroups, tcontent.displaystart, tcontent.displaystop, 0 as comments, tcontentcategoryassign.orderno,tcontent.display,tcontent.active,tcontent.approved,tcontentcategoryassign.featureStart,tcontentcategoryassign.featureStop, tcontentcategoryassign.isFeature, tfiles.fileSize,tfiles.fileExt,tcontent.fileID, tcontent.credits, tcontent.remoteSource, tcontent.remoteURL, tcontent.remoteSourceURL, tcontent.Audience, tcontent.keyPoints from tcontent inner join tcontentcategoryassign ON (tcontent.contenthistid=tcontentcategoryassign.contenthistID) Left Join tfiles ON (tcontent.fileID=tfiles.fileID) where tcontent.active=1 and tcontentcategoryassign.categoryID= AND (tcontent.Display=1 or tcontent.Display = 2 and tcontent.DisplayStart <= #createodbcdatetime(now())# AND (tcontent.DisplayStop >= #createodbcdatetime(now())# or tcontent.DisplayStop is null) ) and (tcontentcategoryassign.isFeature=1 or tcontentcategoryassign.isFeature = 2 and tcontentcategoryassign.FeatureStart <= #createodbcdatetime(now())# AND (tcontentcategoryassign.FeatureStop >= #createodbcdatetime(now())# or tcontentcategoryassign.FeatureStop is null) ) order by tcontent.#arguments.categoryBean.getSortBy()# #arguments.categoryBean.getSortDirection()# tcontentcategoryassign.#arguments.categoryBean.getSortBy()# #arguments.categoryBean.getSortDirection()# SELECT tsettings.Site, tcontentcategories.categoryID, tcontentcategories.name,tcontentcategories.isOpen, count(tcontentcategories2.parentid) as hasKids FROM tsettings INNER JOIN tcontentcategories ON tsettings.SiteID = tcontentcategories.SiteID left join tcontentcategories tcontentcategories2 ON( tcontentcategories.categoryID = tcontentcategories2.parentID) WHERE tcontentcategories.isInterestGroup=1 and tcontentcategories.parentID = is null and tsettings.PrivateUserPoolID = '#variables.instance.settingsManager.getSite(arguments.siteid).getPrivateUserPoolID()#' group by tsettings.Site, tcontentcategories.categoryID, tcontentcategories.name,tcontentcategories.isOpen ORDER BY tsettings.Site, tcontentcategories.name SELECT tsettings.Site, tcontentcategories.categoryID, tcontentcategories.name,tcontentcategories.isOpen, count(tcontentcategories2.parentid) as hasKids FROM tsettings INNER JOIN tcontentcategories ON tsettings.SiteID = tcontentcategories.SiteID left join tcontentcategories tcontentcategories2 ON( tcontentcategories.categoryID = tcontentcategories2.parentID) WHERE tcontentcategories.isInterestGroup =1 and tcontentcategories.parentID = is null and tsettings.PublicUserPoolID = '#variables.instance.settingsManager.getSite(arguments.siteid).getPublicUserPoolID()#' group by tsettings.Site, tcontentcategories.categoryID, tcontentcategories.name,tcontentcategories.isOpen ORDER BY tsettings.Site, tcontentcategories.name