Logic Puzzle Solution Bonus: SQL Server Edition
Reader Johann Preissl writes in with a pure T-SQL solution to the Five-House Logic Puzzle. No tables or VBA required!
This post is a followup to the Five-House Logic Problem. I described how I worked out the solution by hand in an earlier post (warning: contains spoilers). This post is a bonus solution submitted by a reader and written in pure T-SQL.
It took me nearly two hours to solve the Five-House Logic Problem by hand:
Mike Wolfe
It took fellow Access MVP, Maria Barnes, only thirty minutes to solve the problem by hand.
It's already taken me more than two hours to write and test a class module to verify a solution to the problem, and I still haven't gotten around to writing the code to generate all the possible neighborhood permutations so that I can use that class module to brute-force a solution.
In the meantime, reader Johann Preissl emailed me with a pure T-SQL solution that runs in less than a minute (14 seconds on my relatively powerful home computer). I just hope it took him more than a few minutes to write it; I don't know how many more hits to my ego I can take.
The Five-House Logic Problem
As a brief reminder–and for easy reference–here are the fifteen rules and two unanswered questions of the Five-House Logic Problem:
- There are five houses in a row, each of a different color and inhabited by people of different nationalities with different pets, drinks, and flowers.
- The English person lives in the red house.
- The Spaniard owns the dog.
- Coffee is drunk in the green house.
- The Ukrainian drinks tea.
- The green house is immediately to the right (your right) of the ivory house.
- The geranium grower owns snails.
- Roses are in front of the yellow house.
- Milk is drunk in the middle house.
- The Norwegian lives in the first house on the left.
- The person who grows marigolds lives in the house next to the person with the fox.
- Roses are grown at the house next to the house where the horse is kept.
- The person who grows lilies drinks orange juice.
- The Japanese person grows gardenias.
- The Norwegian lives next to the blue house.
Who drinks water? And who owns the zebra?
The T-SQL Solution
Here is reader Johann Preissl's brilliant T-SQL-only solution (shared with permission):
-- Einstein's Five-House Logic Problem pure SQL solution, strait forward and simple
-- runs in SQL Server (no need for an own database, data comes from the VALUES clauses below)
WITH step1 AS
( -- set up the person, color, ... combinations via CROSS JOIN
SELECT housenr, person, color, pet, drink, flower
FROM
(SELECT * FROM (VALUES (1), (2), (3), (4), (5) ) h (housenr) ) AS houses cross join
(SELECT * FROM (VALUES ('English'), ('Spaniard'), ('Ukrainian'), ('Norwegian'), ('Japanese') ) n (person) ) AS persons cross join
(SELECT * FROM (VALUES ('red'), ('green'), ('ivory'), ('blue'), ('yellow') ) c (color) ) AS colors cross join
(SELECT * FROM (VALUES ('dog'), ('snails'), ('fox'), ('horse'), ('zebra') ) p (pet) ) AS pets cross join
(SELECT * FROM (VALUES ('coffee'), ('tea'), ('milk'), ('orange juice'), ('water') ) d (drink) ) AS drinks cross join
(SELECT * FROM (VALUES ('geranium'), ('roses'), ('marigold'), ('lilies'), ('gardenia') ) f (flower) ) AS flowers
WHERE -- conditions based on only one house (row) --> directly reduce the combinations
(person != 'English' and color != 'red' or person = 'English' and color = 'red') and -- rule 2
(person != 'Spaniard' and pet != 'dog' or person = 'Spaniard' and pet = 'dog') and -- rule 3
(drink != 'coffee' and color != 'green' or drink = 'coffee' and color = 'green') and -- rule 4
(person != 'Ukrainian' and drink != 'tea' or person = 'Ukrainian' and drink = 'tea') and -- rule 5
(flower != 'geranium' and pet != 'snails' or flower = 'geranium' and pet = 'snails') and -- rule 7
(flower != 'roses' and color != 'yellow' or flower = 'roses' and color = 'yellow') and -- rule 8
(housenr != 3 and drink != 'milk' or housenr = 3 and drink = 'milk') and -- rule 9
(person != 'Norwegian' and housenr != 1 or person = 'Norwegian' and housenr = 1) and -- rule 10
(flower != 'lilies' and drink != 'orange juice' or flower = 'lilies' and drink = 'orange juice') and -- rule 13
(person != 'Japanese' and flower != 'gardenia' or person = 'Japanese' and flower = 'gardenia') -- rule 14
)
--SELECT * FROM step1
-- now set the 5 houses in one row
SELECT * -- ON clauses used for rule 1 --> different values on person, color, ...
FROM (SELECT * FROM step1 WHERE housenr = 1) AS houseone inner join
(SELECT * FROM step1 WHERE housenr = 2) AS housetwo ON houseone.person != housetwo.person and
houseone.color != housetwo.color and
houseone.pet != housetwo.pet and
houseone.drink != housetwo.drink and
houseone.flower != housetwo.flower
inner join
(SELECT * FROM step1 WHERE housenr = 3) AS housethree ON houseone.person != housethree.person and
houseone.color != housethree.color and
houseone.pet != housethree.pet and
houseone.drink != housethree.drink and
houseone.flower != housethree.flower and
housetwo.person != housethree.person and
housetwo.color != housethree.color and
housetwo.pet != housethree.pet and
housetwo.drink != housethree.drink and
housetwo.flower != housethree.flower
inner join
(SELECT * FROM step1 WHERE housenr = 4) AS housefour ON houseone.person != housefour.person and
houseone.color != housefour.color and
houseone.pet != housefour.pet and
houseone.drink != housefour.drink and
houseone.flower != housefour.flower and
housetwo.person != housefour.person and
housetwo.color != housefour.color and
housetwo.pet != housefour.pet and
housetwo.drink != housefour.drink and
housetwo.flower != housefour.flower and
housethree.person != housefour.person and
housethree.color != housefour.color and
housethree.pet != housefour.pet and
housethree.drink != housefour.drink and
housethree.flower != housefour.flower
inner join
(SELECT * FROM step1 WHERE housenr = 5) AS housefive ON houseone.person != housefive.person and
houseone.color != housefive.color and
houseone.pet != housefive.pet and
houseone.drink != housefive.drink and
houseone.flower != housefive.flower and
housetwo.person != housefive.person and
housetwo.color != housefive.color and
housetwo.pet != housefive.pet and
housetwo.drink != housefive.drink and
housetwo.flower != housefive.flower and
housethree.person != housefive.person and
housethree.color != housefive.color and
housethree.pet != housefive.pet and
housethree.drink != housefive.drink and
housethree.flower != housefive.flower and
housefour.person != housefive.person and
housefour.color != housefive.color and
housefour.pet != housefive.pet and
housefour.drink != housefive.drink and
housefour.flower != housefive.flower
WHERE (houseone.color = 'ivory' and housetwo.color = 'green' or -- rule 6
housetwo.color = 'ivory' and housethree.color = 'green' or
housethree.color = 'ivory' and housefour.color = 'green' or
housefour.color = 'ivory' and housefive.color = 'green' ) and
(houseone.flower = 'marigold' and housetwo.pet = 'fox' or -- rule 11
housetwo.flower = 'marigold' and (houseone.pet = 'fox' or housethree.pet = 'fox') or
housethree.flower = 'marigold' and (housetwo.pet = 'fox' or housefour.pet = 'fox') or
housefour.flower = 'marigold' and (housethree.pet = 'fox' or housefive.pet = 'fox') or
housefive.flower = 'marigold' and housefour.pet = 'fox' ) and
(houseone.flower = 'roses' and housetwo.pet = 'horse' or -- rule 12
housetwo.flower = 'roses' and (houseone.pet = 'horse' or housethree.pet = 'horse') or
housethree.flower = 'roses' and (housetwo.pet = 'horse' or housefour.pet = 'horse') or
housefour.flower = 'roses' and (housethree.pet = 'horse' or housefive.pet = 'horse') or
housefive.flower = 'roses' and housefour.pet = 'horse' ) and
(houseone.color = 'blue' and housetwo.person = 'Norwegian' or -- rule 15
housetwo.color = 'blue' and (houseone.person = 'Norwegian' or housethree.person = 'Norwegian') or
housethree.color = 'blue' and (housetwo.person = 'Norwegian' or housefour.person = 'Norwegian') or
housefour.color = 'blue' and (housethree.person = 'Norwegian' or housefive.person = 'Norwegian') or
housefive.color = 'blue' and housefour.person = 'Norwegian' ) Results
Here are the full results screenshotted from the SQL Server Management Studio Results pane:
How It Works
Here's a plain English explanation of how Johann's T-SQL works, courtesy of ChatGPT:
The T-SQL code provided solves the logic puzzle using a combination of CTEs (Common Table Expressions) and joins. Let's break down how the code works step by step:
- CTE Setup (
step1): The code starts by defining a CTE namedstep1which sets up all the possible combinations of house numbers, persons, colors, pets, drinks, and flowers. It uses a series of CROSS JOIN operations on subqueries containing the values for each attribute. The WHERE clause of the CTE filters out combinations that violate the given conditions of the puzzle. - Joining the Houses: The next section of the code joins the five houses together in a row. Each house is represented by a separate subquery that selects the appropriate rows from the
step1CTE based on the house number.
The join conditions ensure that each attribute (person, color, pet, drink, flower) is different for each house. This eliminates combinations where two houses have the same attribute, violating the puzzle rules.
- Applying Puzzle Rules: After joining the houses, the code applies the remaining rules of the puzzle using additional WHERE clauses. These clauses check for specific combinations of attributes that satisfy the given rules. For example, rule 6 states that the green house is immediately to the right of the ivory house. This is represented by the condition
(houseone.color = 'ivory' and housetwo.color = 'green')in the WHERE clause. - Result: The final result of the query is the set of rows that satisfy all the conditions. Each row represents a unique combination of attributes for the five houses, where each house has a different person, color, pet, drink, and flower. The result provides the information needed to answer the questions: "Who drinks water?" and "Who owns the zebra?"
By executing the T-SQL code, you would obtain the specific rows that satisfy all the conditions, and you can then examine the values in those rows to determine the answers to the puzzle's questions.
High Praise for NoLongerSet.com
In his email, Johann left me with a post-script that absolutely made my day:
Thanks for the excellent quality of your blog, after Scott Hanselman got tired you are my favorite
I'll take second place behind Scott Hanselman any day. That's some rarified air.
Thank you, Johann, for your insightful T-SQL solution and your exceptionally kind words.
Acknowledgements
- Portions of this article's body generated with the help of ChatGPT
