Report to get the list of Category, Subcategory and Item.
Use the query to list of Category, Subcategory and Item.
MYSQL database:
select cat.categoryname "Category", sub.name "Subcategory", GROUP_CONCAT(item.name separator '\n') "Item" from categorydefinition cat left join subcategorydefinition sub on cat.categoryid=sub.categoryid left join itemdefinition item on sub.subcategoryid=item.subcategoryid group by cat.categoryname, sub.name
MSSQL database:
SELECT cat.CategoryName "Category" , sub.NAME "Subcategory",(SELECT STUFF((SELECT NAME + CHAR(10) FROM ItemDefinition item where sub.SUBCATEGORYID=Item.SUBCATEGORYID
order by 1 FOR XML PATH ('')), 1, 0, '')) "Item" FROM CategoryDefinition cat LEFT JOIN SubCategoryDefinition sub ON cat.CATEGORYID=sub.CATEGORYID
Regards,
Stephen
MYSQL database:
select cat.categoryname "Category", sub.name "Subcategory", GROUP_CONCAT(item.name separator '\n') "Item" from categorydefinition cat left join subcategorydefinition sub on cat.categoryid=sub.categoryid left join itemdefinition item on sub.subcategoryid=item.subcategoryid group by cat.categoryname, sub.name
MSSQL database:
SELECT cat.CategoryName "Category" , sub.NAME "Subcategory",(SELECT STUFF((SELECT NAME + CHAR(10) FROM ItemDefinition item where sub.SUBCATEGORYID=Item.SUBCATEGORYID
order by 1 FOR XML PATH ('')), 1, 0, '')) "Item" FROM CategoryDefinition cat LEFT JOIN SubCategoryDefinition sub ON cat.CATEGORYID=sub.CATEGORYID
Regards,
Stephen