Database Challenges

--tsqlchallenge_22_v3.sql
declare @Result table (BoxId int, BallId int, primary key clustered (BoxId, BallId))
declare @NumOfBalls int

select @NumOfBalls = (select count(t.BallId) from TC22_Balls t)

-- additional section - first iteration (less reads)
insert into @Result
select dp.BoxId,dp.BallId from (select distinct tp.BoxId,tp.BallId from TC22_Preferences tp) dp
where
	dp.BallId in
		(select bl.BallId from (select distinct tp.BoxId,tp.BallId from TC22_Preferences tp) bl
		group by bl.BallId
		having count(bl.BallId)=1)
	or
	dp.BoxId in
		(select bo.BoxId from (select distinct tp.BoxId,tp.BallId from TC22_Preferences tp) bo
		group by bo.BoxId
		having count(bo.BoxId)=1)

-- loop
while (select count(r.BallId) from @Result r)!=@NumOfBalls 
begin
	insert into @Result
	select dp.BoxId,dp.BallId
	from (select distinct tp.BoxId,tp.BallId from TC22_Preferences tp) dp
	where
	(
	dp.BallId in
		(select bl.BallId from (select distinct tp.BoxId,tp.BallId from TC22_Preferences tp) bl
			where bl.ballId not in (select BallId from @Result)
			and bl.boxid not in (select BoxId from @Result)
		group by bl.BallId
		having count(bl.BallId)=1)
	or dp.BoxId in
		(select bo.BoxId from (select distinct tp.BoxId,tp.BallId from TC22_Preferences tp) bo
			where bo.BoxId not in (select BoxId from @Result)
			and bo.BallId not in (select BallId from @Result)
		group by bo.BoxId
		having count(bo.BoxId)=1)
	)
	and	dp.BoxId not in (select BoxId from @Result)
	and dp.BallId not in (select BallId from @Result)
end

-- final select
select bo.BoxName,bl.BallName from @Result r
join TC22_Balls bl on bl.BallId=r.BallId
join TC22_Boxes bo on bo.BoxId=r.BoxId
order by r.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.