SELECT [Id]
,[Name]
,[Description]
,[CategoryTemplateId]
,[MetaKeywords]
,[MetaDescription]
,[MetaTitle]
,[ParentCategoryId]
,[PictureId]
,[PageSize]
,[AllowCustomersToSelectPageSize]
,[PageSizeOptions]
,[PriceRanges]
,[ShowOnHomePage]
,[IncludeInTopMenu]
,[SubjectToAcl]
,[LimitedToStores]
,[Published]
,[Deleted]
,[DisplayOrder]
,[CreatedOnUtc]
,[UpdatedOnUtc]
FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [Id]
FROM [YourDatabase].[dbo].[Product_Category_Mapping])
DELETE FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [Id]
FROM [YourDatabase].[dbo].[Product_Category_Mapping])
SELECT [Id]
,[Name]
,[Description]
,[CategoryTemplateId]
,[MetaKeywords]
,[MetaDescription]
,[MetaTitle]
,[ParentCategoryId]
,[PictureId]
,[PageSize]
,[AllowCustomersToSelectPageSize]
,[PageSizeOptions]
,[PriceRanges]
,[ShowOnHomePage]
,[IncludeInTopMenu]
,[SubjectToAcl]
,[LimitedToStores]
,[Published]
,[Deleted]
,[DisplayOrder]
,[CreatedOnUtc]
,[UpdatedOnUtc]
FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [CategoryId]
FROM [YourDatabase].[dbo].[Product_Category_Mapping])
DELETE FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [CategoryId]
FROM [YourDatabase].[dbo].[Product_Category_Mapping])
DELETE FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [CategoryId]
FROM [YourDatabase].[dbo].[Product_Category_Mapping]
) AND [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [ParentCategoryId]
FROM [YourDatabase].[dbo].[Category]
WHERE [Id] IN (
SELECT [CategoryId]
FROM [YourDatabase].[dbo].[Product_Category_Mapping]
)) AND [Published] != 0
SELECT *
FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [CategoryId]
FROM [YourDatabase].[dbo].[Product_Category_Mapping]
) AND [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [ParentCategoryId]
FROM [YourDatabase].[dbo].[Category]
WHERE [Id] IN (
SELECT [CategoryId]
FROM [YourDatabase].[dbo].[Product_Category_Mapping]
)) AND [Published] != 0
SELECT c.Id
,c.Name
,c.ParentCategoryId
,c.Published
,c.Deleted
FROM Category c
LEFT JOIN
Product_Category_Mapping pcm
ON pcm.CategoryId = c.Id
LEFT JOIN
Category c2
ON c2.ParentCategoryId = c.Id
WHERE pcm.Id IS NULL
AND c2.Id IS NULL
DELETE c
FROM Category c
LEFT JOIN
Product_Category_Mapping pcm
ON pcm.CategoryId = c.Id
LEFT JOIN
Category c2
ON c2.ParentCategoryId = c.Id
WHERE pcm.Id IS NULL
AND c2.Id IS NULL
UPDATE c
SET c.Published = 0
FROM Category c
LEFT JOIN
Product_Category_Mapping pcm
ON pcm.CategoryId = c.Id
LEFT JOIN
Category c2
ON c2.ParentCategoryId = c.Id
AND c2.Published = 1
WHERE pcm.Id IS NULL
AND c2.Id IS NULL
SELECT c.Id
,c.Name
,c.ParentCategoryId
,c.Published
,c.Deleted
FROM Category c
WHERE Published = 1
AND Deleted = 0
--Exclude categories that are parents of other published categories
AND Id NOT IN (SELECT Category.ParentCategoryId
FROM Category
WHERE Published = 1
AND Deleted = 0)
--Exclude categories that are parents of published products
AND Id NOT IN (SELECT CategoryId
FROM Product_Category_Mapping
JOIN
Product
ON ProductId = Product.Id
AND Product.Published = 1
AND Product.Deleted = 0
AND Product.StockQuantity > 0)