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!

Logic Puzzle Solution Bonus: SQL Server Edition

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:

Solving the Five-House Logic Problem by Hand
In this follow-up to my post on the classic Five-House Logic Problem, I provide the solution to the riddle plus a few tips for finding it.

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:

  1. 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.
  2. The English person lives in the red house.
  3. The Spaniard owns the dog.
  4. Coffee is drunk in the green house.
  5. The Ukrainian drinks tea.
  6. The green house is immediately to the right (your right) of the ivory house.
  7. The geranium grower owns snails.
  8. Roses are in front of the yellow house.
  9. Milk is drunk in the middle house.
  10. The Norwegian lives in the first house on the left.
  11. The person who grows marigolds lives in the house next to the person with the fox.
  12. Roses are grown at the house next to the house where the horse is kept.
  13. The person who grows lilies drinks orange juice.
  14. The Japanese person grows gardenias.
  15. 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
            (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' )  


Here are the full results screenshotted from the SQL Server Management Studio Results pane:

Open image in new tab or window to view full size

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:

  1. CTE Setup (step1): The code starts by defining a CTE named step1 which 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.
  2. 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 step1 CTE 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.

  1. 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.
  2. 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.

  • Portions of this article's body generated with the help of ChatGPT

All original code samples by Mike Wolfe are licensed under CC BY 4.0