Database Challenges

--Pragnesh_Patel_tsqlchallenge_22.sql
--SET STATISTICS IO ON
--SET STATISTICS TIME ON

--DBCC DROPCLEANBUFFERS 
--DBCC FREEPROCCACHE

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

DECLARE @PendBoxCntr AS INT
DECLARE @PendBallCntr AS INT

SELECT @PendBoxCntr = Count(BoxID) FROM TC22_Boxes
SELECT @PendBallCntr = Count(BallID) FROM TC22_Balls

WHILE @PendBoxCntr <> 0	OR @PendBallCntr <> 0

	BEGIN

		INSERT INTO @TC22_Result ( BoxID, BallID )
		SELECT TC22_Preferences.BoxID, TC22_Preferences.BallID
		FROM TC22_Preferences
		WHERE TC22_Preferences.BoxID IN (
											SELECT BoxID
											FROM TC22_Preferences
											WHERE BoxID Not IN ( SELECT BoxID FROM @TC22_Result )
												AND BallID Not IN ( SELECT BallID FROM @TC22_Result )
											GROUP BY BoxID
											HAVING Count(BoxID) = 1
										)
				AND BallID Not IN ( SELECT BallID FROM @TC22_Result )


		INSERT INTO @TC22_Result ( BoxID, BallID )
		SELECT TC22_Preferences.BoxID, TC22_Preferences.BallID
		FROM TC22_Preferences
		WHERE TC22_Preferences.BallID IN (
											SELECT BallID
											FROM TC22_Preferences
											WHERE BallID Not IN ( SELECT BallID FROM @TC22_Result )
												AND BoxID Not IN ( SELECT BoxID FROM @TC22_Result )
											GROUP BY BallID
											HAVING Count(BallID) = 1
										)
				AND BoxID Not IN ( SELECT BoxID FROM @TC22_Result )

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

		SELECT @PendBallCntr = Count( DISTINCT BallID)
		FROM TC22_Preferences
		WHERE BallID Not IN ( SELECT BallID 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.