Winners and Solution – DELETE Qualified Rows From Multiple Tables

Earlier this week, I had announced a contest to get Recorded PASS of my extremely popular training SQL Server Performance Tuning Practical Workshop – Recorded Classes. As the price of this class high, I wanted to keep my contest difficult as well. Today we will see the winners and solutions.

You can read this blog post to understand what was the actual puzzle and conditions. Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class. Here I am reproducing the puzzle in the simple words –

Due to some reason, in the school, the class of English was canceled during the summer. All the students who had enrolled in the English class were affected. Here are two scenarios for us for the students who had taken English class.

Scenario 1: If a student has enrolled in a single class of English – Remove from the Student’s list and Remove from the StudentClass list.
Scenario 2: If a student has enrolled in multiple class with English class – Remove from the StudentClass list.

Original Setup

What we want to do

Final Outcome

To active the final outcome from the original setup, we needed to write a query which takes the least amount of the reads. I had given a sample query which was taking 19 logical reads and that was way higher. Your task was to come up with the solution which takes lease amount of the logical reads to accomplish my tasks.

I was very much delighted that I have got many good answers to this DIFFICULT puzzle.

Let us see first the solution of Harsh who has won the FREE Access to the SQL Server Performance Tuning Practical Workshop – Recorded Classes.

Solution by Harsh – Winner

Only 4 logical reads.

SET STATISTICS IO ON
DECLARE @ClassID AS INT
SELECT @ClassID=ID FROM Class WHERE ClassName='English'
--
DECLARE @StudentID AS VARCHAR(MAX)
SELECT @StudentID= COALESCE(@StudentID , ',') + CAST(StudentID AS VARCHAR(20)) + ','
FROM StudentClass
GROUP BY StudentID
HAVING COUNT(*) = 1 AND MIN(ClassID)=@ClassID
--
DELETE FROM Student WHERE @StudentID LIKE '%,' + CAST(ID AS VARCHAR(20)) + ',%'
DELETE FROM StudentClass WHERE ClassID=@ClassID
GO

Harsh, send me email to get your FREE access as you are winner. Here is the link to his solution.

Notable Winners

Here are other participants who also provided a valid answer and also eligible for 50% off for my recorded course.

• sabin
• Calunoth Allerton
• Andrew Wait
• Nandakumar
• Sanjay Monpara
• Bryan Rebok
• Saranyasc
• Nitin Dhiman

If you can think of the better solution, you can always post them here.

Reference: Pinal Dave (https://blog.sqlauthority.com)