Database Challenges

--Pragnesh_Patel_tsqlchallenge_22_v4.sql
--SET STATISTICS IO ON
--SET STATISTICS TIME ON
--
--DBCC DROPCLEANBUFFERS 
--DBCC FREEPROCCACHE

DECLARE @TC22_Result AS TABLE
(
    BoxId INT,
    BallID INT
)

DECLARE @TC22_PendPreferences AS TABLE
(
    BoxId INT,
	BallId INT
)

DECLARE @PendBoxCntr AS INT



SELECT @PendBoxCntr = Count(BoxID) FROM TC22_Boxes

INSERT INTO @TC22_PendPreferences ( BoxID, BallID )
SELECT BoxID, BallID FROM TC22_Preferences

WHILE @PendBoxCntr <> 0

	BEGIN

		INSERT INTO @TC22_Result ( BoxID, BallID )
		SELECT PendPref.BoxID, PendPref.BallID
		FROM @TC22_PendPreferences AS PendPref
		WHERE PendPref.BoxID IN (
									SELECT BoxID
									FROM @TC22_PendPreferences AS PendPref
									GROUP BY BoxID
									HAVING Count(BoxID) = 1
								)

		-- Delete Records from PendPreferences Which are Set
		DELETE FROM @TC22_PendPreferences
		WHERE BoxID IN ( SELECT BoxID FROM @TC22_Result )
			OR BallID IN ( SELECT BallID FROM @TC22_Result )


		INSERT INTO @TC22_Result ( BoxID, BallID )
		SELECT PendPref.BoxID, PendPref.BallID
		FROM @TC22_PendPreferences AS PendPref
		WHERE PendPref.BallID IN (
									SELECT BallID
									FROM @TC22_PendPreferences AS PendPref
									GROUP BY BallID
									HAVING Count(BallID) = 1
								)

		-- Delete Records from PendPreferences Which are Set
		DELETE FROM @TC22_PendPreferences
		WHERE BoxID IN ( SELECT BoxID FROM @TC22_Result )
			OR BallID IN ( SELECT BallID FROM @TC22_Result )


		SELECT @PendBoxCntr = Count(BoxID)
		FROM TC22_Boxes
		WHERE BoxID Not IN ( SELECT BoxID FROM @TC22_Result )

	END


SELECT Box=TC22_Boxes.BoxName, Ball=TC22_Balls.BallName
FROM @TC22_Result AS TC22_Result
	INNER JOIN TC22_Boxes ON TC22_Boxes.BoxID = TC22_Result.BoxID
	INNER JOIN TC22_Balls ON TC22_Balls.BallID = TC22_Result.BallID
ORDER BY TC22_Result.BoxID

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.