One of my good friends approached me this morning with this scenario.
1. Parent Id and ChildId are stored in the same table.
2. The relationship goes only one level deep (i.e. a child record is not a parent record for any other child record)
Requirement:
He wanted a combined count of
1. All Parent Ids that do not have any child records
+
2. Count of all Child records.
Here is the script I wrote and gave him.
Hope it is useful to you as well.. :-)
-- CREATE A TABLE FOR OUR SCENARIO
CREATE TABLE T1 (ID INT IDENTITY PRIMARY KEY, PARENT INT REFERENCES T1(ID))
GO
--POPULATE THE TABLE FOR OUR SCENARIO
-- EXECUTE NEXT 6 INSERT STATEMENTS..THESE 6 STATEMENTS SHOULD CREATE
-- ABOUT 25 RECORDS OUT OF WHICH 20 RECORDS WILL QUALIFY FOR OUR SCENARIO
INSERT T1 (PARENT) VALUES (NULL)
GO 10
INSERT INTO T1 (PARENT) VALUES (1)
GO
INSERT INTO T1 (PARENT) VALUES (2)
GO 2
INSERT INTO T1 (PARENT) VALUES (3)
GO 3
INSERT INTO T1 (PARENT) VALUES (4)
GO 4
INSERT INTO T1 (PARENT) VALUES (5)
GO 5
-- DO A SELECT TO MAKE SURE IT LOOKS GOOD.
SELECT * FROM T1
GO
--EXECUTE THE NEXT QUERY THAT GIVES THE RESULTS.
WITH COUNT1(ID) AS
( SELECT COUNT(DISTINCT a.ID) ID
FROM T1 a
WHERE PARENT IS NULL
AND NOT EXISTS (SELECT 1 FROM T1 b WHERE a.ID = b.PARENT)
UNION
SELECT COUNT(DISTINCT t2.ID) ID
FROM T1 t2
WHERE PARENT IS NOT NULL
)
SELECT SUM(ID)
FROM COUNT1
GO
