SQL SERVER – The Two Doors, Two Guards Puzzle

Recently, I came across a puzzle about “The Two Doors, Two Guards,” popularly known as the “Two Doors Riddle” or the “Heaven and Hell Riddle”. I thought it would be interesting to convert it to an SQL problem and solve it. I have spent quite a lot of time and build logic in SQL. The logic is not handling all the situations. It is not doing any error handling or working with NULL. My goal was to see if I could build a problem statement and solve it with T-SQL. I will be very happy if you give me guidance if I have missed anything in logic. Let us first see the puzzle and its solution.

The Challenge

Imagine yourself as a prisoner in a room with two doors and two guards. One door leads to freedom, and the other leads to certain demise. Unfortunately, you don’t know which door leads where.

As for the guards, one always tells the truth, and the other always lies. Again, you don’t know which guard is the truth-teller and which is the liar. However, both guards know which is the truth-teller and which is the liar.

You have the opportunity to ask a single question to one of the guards before you choose and open one of the doors. But remember, you can’t ask more than one question, and it can only be directed to one guard.

The Solution

The key to the puzzle is to ask a question that will yield the same answer, regardless of whether you’re asking the truth-teller or the liar.

The question you should ask is: “If I were to ask the other guard which door leads to freedom, which door would he point to?”

Here are the two scenarios:

• If you asked the truth-telling guard, he’d tell you that the liar would point to the door leading to the hangman because the liar would indicate the wrong door.
• If you asked the lying guard, he’d lie and tell you that the truth-telling guard would point to the door leading to the hangman, even though the truth-teller would actually point to the door leading to freedom.

In both cases, the guard you ask will point to the door leading to the hangman. Therefore, to escape, you should choose the other door.

This classic logic puzzle demonstrates how to arrive at a truthful answer even when dealing with unreliable information sources.

T-SQL The Two Doors, Two Guards Puzzle

After reading the puzzle, I first decided to write it down using some variable so I could easily translate it into T-SQL. Here is my brought draft of it.

The Challenge

Imagine you are a prisoner in a room with two doors and two guards. Each door and each guard is represented in a SQL database, with the Doors table representing the doors and the Guards table representing the guards.

In the Doors table, one of the doors has a LeadsToFreedom value of 1, denoting that it leads to freedom, while the other has a LeadsToFreedom value of 0, indicating it leads to a grim fate.

The Guards table has an IsTruthTeller value for each guard. One guard always tells the truth (IsTruthTeller value is 1), and the other always lies (IsTruthTeller value is 0). Both guards are aware of each other’s truth-telling status.

Your challenge is to open one of the doors, but you can only ask a single question to one of the guards using the AskGuard stored procedure.

The Solution

To solve this puzzle using SQL, you can ask one guard the following question: “Which door would the other guard point to?“.

The AskGuard stored procedure will return the name of the door that the other guard would point to, but there’s a twist:

• If you asked the truth-telling guard, he would truthfully tell you that the liar guard would point to the door that leads to the hangman.
• If you asked the liar guard, he would lie and tell you that the truth-telling guard would point to the door that leads to the hangman.

In both cases, AskGuard gives you the name of the same door, which is the one that leads to the hangman. So, you should choose the other door to gain your freedom.

T-SQL Script for Puzzle:

-- Create table to store Guard details
CREATE TABLE Guards (
ID INT PRIMARY KEY,
Name VARCHAR(255),
IsTruthTeller BIT -- Column to indicate if guard is a truth-teller
);
GO
-- Create table to store Door details
CREATE TABLE Doors (
ID INT PRIMARY KEY,
LeadsToFreedom BIT, -- Column to indicate if door leads to freedom
DoorName VARCHAR(255)
);
GO
INSERT INTO Guards (ID, Name, IsTruthTeller) VALUES
(1, 'Guard 1', 1), -- Truth-teller Guard
(2, 'Guard 2', 0); -- Liar Guard
GO
INSERT INTO Doors (ID, LeadsToFreedom, DoorName) VALUES
(1, 1, 'Door 1'), -- Door that leads to freedom
(2, 0, 'Door 2'); -- Door that doesn't lead to freedom
GO
-- Stored procedure to ask guard which is the door to freedom
CREATE OR ALTER PROCEDURE AskGuard 
    @GuardID INT
AS
BEGIN
    DECLARE @OtherGuardID INT;
    DECLARE @IsTruthTeller BIT, @OtherGuardResponse VARCHAR(255);
    
    SELECT @OtherGuardID = CASE WHEN @GuardID = 1 THEN 2 ELSE 1 END;
    
    SELECT @IsTruthTeller = IsTruthTeller FROM Guards WHERE ID = @GuardID;

    -- Determine what the other guard would say about the door that leads to freedom
    SELECT @OtherGuardResponse = CASE 
        WHEN IsTruthTeller = 1 THEN (SELECT DoorName FROM Doors WHERE LeadsToFreedom = 1)
        ELSE (SELECT DoorName FROM Doors WHERE LeadsToFreedom = 0)
    END FROM Guards WHERE ID = @OtherGuardID;
    
    SELECT CASE WHEN @IsTruthTeller = 1 THEN @OtherGuardResponse ELSE 
        (SELECT DoorName FROM Doors WHERE DoorName <> @OtherGuardResponse) END 
		AS 'Which door would the other guard point to? Select Opposite Door.';
END;
GO
-- Execute stored procedure for each guard
EXEC AskGuard 1;
EXEC AskGuard 2;
GO
-- Cleanup
DROP PROCEDURE AskGuard;
DROP TABLE Guards;
DROP TABLE Doors;
GO

In the current setup, both queries will lead to Door 2, which means we should choose Door 1 for freedom. You can verify this with different setups, such as changing which door is for freedom or which guard is truth-telling, and it will work every time.

As I mentioned earlier, this solution may not work if the input is incorrect, such as when both guards are truth-telling or liars or when both doors lead to freedom or the hangman. Additionally, inserting more than 2 rows in the table may not work as expected.

My intention was not to create a perfect query but to attempt a T-SQL solution for this classic puzzle.

If you have an alternative solution, please share it; I would love to learn from it. You can tweet to me.

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