| |
SELECT
categoryname, c.categoryid,
categorydescription, c.parentid, childlevel,
isNull(plist.cntofchildren, 0) 'countofchildren',
showanonymous, isNull(cnt, 0) AS countofproducts
FROM (select * from tblprodcategories where isNull(fk_storeid, 0) in(0, 143)) c
LEFT JOIN
(SELECT categoryid, count(*) AS cnt FROM tblcategoryproductbridge
WHERE productid in(SELECT productid FROM tblproducts WHERE discontinued = 0)
group by categoryid ) cn
ON c.categoryid = cn.categoryid
LEFT JOIN (SELECT parentid , count(*) cntofchildren FROM (select * from tblprodcategories where isNull(fk_storeid, 0) in(0, 143)) ccount group by parentid) plist
ON c.categoryid = plist.parentid
WHERE c.CategoryID IN(
SELECT CategoryID
FROM ut_GroupCategoryPermissions
WHERE GroupID IN(
SELECT GroupID
FROM ut_UserGroupMemberShip
WHERE UserID=0
)
UNION
SELECT CategoryID
FROM tblCategorySecurity
WHERE UserID= 0
)
OR (0 = 0 AND showanonymous = 1)
order by childlevel asc, isNull(sortorder, 100000), categoryname
|
