August 14, 2005 at 9:13 pm
I have a hierarchical table like this:
CatID CatName ParentID
1 All Categories null
2 Restuarants 1
3 Italian Rest 2
4 Fast Food Joints 1
etc and so on.
My other table is like this
ListingID Cat1ID Cat2ID Cat3ID ListingName
1 4 2 null McDonalds
2 2 4 8 KFC
Only Cat1ID is compulsory, both Cat2 and Cat3ID are optional.
The question is for a given CategoryID for example 2, it has to give me a count of listings found in All the categories (cat1, cat2 and cat3) as well as their inner level categories.
Your help is greatly appreciated.
August 15, 2005 at 3:35 am
You can pick and choose what you need.
if exists (select * from sysobjects where id = object_id('Category') and sysstat & 0xf = 3)
drop table Category
GO
CREATE TABLE Category (CatID int not null,CatName varchar(30),ParentID int)
GO
INSERT INTO Category
SELECT 1,'All Categories',null
UNION
SELECT 2,'Restuarants',1
UNION
SELECT 3,'Italian Rest',2
UNION
SELECT 4,'Fast Food Joints',1
GO
if exists (select * from sysobjects where id = object_id('Listing') and sysstat & 0xf = 3)
drop table Listing
GO
CREATE TABLE Listing (ListingID int not null,Cat1ID int,Cat2ID int,Cat3ID int,ListingName varchar(30))
GO
INSERT INTO Listing
SELECT 1,4,2,null,'McDonalds'
UNION
SELECT 2,2,4,8,'KFC'
GO
-- Use Temp table to combine for counting
DECLARE @List TABLE (CatID int,ListingName varchar(30),ListingID int)
INSERT INTO @List(CatID,ListingName,ListingID)
SELECT Cat1ID AS CatID,ListingName,ListingID
FROM Listing AS Cat1
WHERE Cat1ID IS NOT NULL
UNION ALL
SELECT Cat2ID AS CatID,ListingName,ListingID
FROM Listing AS Cat2
WHERE Cat2ID IS NOT NULL
UNION ALL
SELECT Cat3ID AS CatID,ListingName,ListingID
FROM Listing AS Cat3
WHERE Cat3ID IS NOT NULL
GO
-- Here is the Query
SELECT Category.CatID, Category.CatName
, S1.CatName AS Sub1CatName, S2.CatName AS Sub2CatName, L1.ListingName
, COUNT(A.ListingID) AS AllListings
, COUNT(DISTINCT L1.ListingID) AS Listings
, COUNT(DISTINCT S1.CatID) AS Sub1Cnt
, COUNT(DISTINCT S2.CatID) AS Sub2Cnt
FROM Category
LEFT JOIN (SELECT CAST(1 AS int) AS CatID, ListingID
FROM @List) AS A ON Category.CatID = A.CatID
LEFT JOIN (SELECT ParentID,CatName,CatID
FROM Category WHERE ParentID=1
GROUP BY ParentID,CatName,CatID) S1 ON Category.CatID = S1.ParentID
LEFT JOIN (SELECT ParentID,CatName, CatID
FROM Category WHERE ParentID=2
GROUP BY ParentID,CatName,CatID) S2 ON S1.CatID = S2.ParentID
LEFT JOIN (SELECT CatID,ListingName,ListingID
FROM @List
GROUP BY CatID,ListingName,ListingID) AS L1 ON S1.CatID = L1.CatID
WHERE Category.CatID = 1
GROUP BY Category.CatID,Category.CatName,S1.CatName,S2.CatName,L1.ListingName
ORDER BY Category.CatID,Category.CatName,S1.CatName,S2.CatName,L1.ListingName
GO
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply