| |
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, 125)) 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, 125)) ccount group by parentid) plist
ON c.categoryid = plist.parentid
WHERE c.display = 1 and ShowAnonymous=1
order by childlevel asc, isNull(SortOrder, 100000), categoryname
|
