x

Einstein's Riddle

Einstein's Riddle: Einstein wrote the following riddle. He said that 98% of the world could not solve it. But several NIEHS scientists were able to solve it, and they said it's not all that hard if you pay attention and are very patient. Give it a try:

There are 5 houses in 5 different colors in a row. In each house lives a person with a different nationality. The 5 owners drink a certain type of beverage, smoke a certain brand of cigar, and keep a certain pet. No owners have the same pet, smoke the same brand of cigar, or drink the same beverage. Other facts:

  1. The Brit lives in the red house.
  2. The Swede keeps dogs as pets.
  3. The Dane drinks tea.
  4. The green house is on the immediate left of the white house.
  5. The green house's owner drinks coffee.
  6. The owner who smokes Pall Mall rears birds.
  7. The owner of the yellow house smokes Dunhill.
  8. The owner living in the center house drinks milk.
  9. The Norwegian lives in the first house.
  10. The owner who smokes Blends lives next to the one who keeps cats.
  11. The owner who keeps the horse lives next to the one who smokes Dunhill.
  12. The owner who smokes Bluemasters drinks beer.
  13. The German smokes Prince.
  14. The Norwegian lives next to the blue house.
  15. The owner who smokes Blends lives next to the one who drinks water.

The question is: WHO OWNS THE FISH?

I thought this would be fun solving with SQL--I threw a quick answer below, anyone have a more efficient answer?

Scot
more ▼

asked Aug 31, 2011 at 09:18 PM in Default

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

Cool, now 100% of people can solve it. Take that Einstein. :)
Sep 01, 2011 at 02:13 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
;with housecolor as (
    select 'red' [housecolor] union all select 'green' union all select 'yellow' union all select 'blue' union all select 'white'
)
, pet as (
    select 'FISH' [pet] union all select 'dog' union all select 'cat' union all select 'bird' union all select 'horse'
)
, nationality as  (
    select 'swede' [nationality] union all select 'norwegian' union all select 'german' union all select 'dane' union all select 'brit'
)
, smoke as (
    select 'pall mall' [smoke] union all select 'blends' union all select 'dunhill' union all select 'bluemasters' union all select 'prince'
)
, drink as (
    select 'tea' [drink] union all select 'coffee' union all select 'milk' union all select 'beer' union all select 'water'
)
, houseorder as (
    select 1 [houseorder] union all select 2 union all select 3 union all select 4 union all select 5
)
select hc1.[housecolor] [hc1 housecolor],ho1.[houseorder] [ho1 houseorder],p1.[pet] [p1 pet],n1.[nationality] [n1 nationality],s1.[smoke] [s1 smoke],d1.[drink] [d1 drink]
      ,hc2.[housecolor] [hc2 housecolor],ho2.[houseorder] [ho2 houseorder],p2.[pet] [p2 pet],n2.[nationality] [n2 nationality],s2.[smoke] [s2 smoke],d2.[drink] [d2 drink]
      ,hc3.[housecolor] [hc3 housecolor],ho3.[houseorder] [ho3 houseorder],p3.[pet] [p3 pet],n3.[nationality] [n3 nationality],s3.[smoke] [s3 smoke],d3.[drink] [d3 drink]
      ,hc4.[housecolor] [hc4 housecolor],ho4.[houseorder] [ho4 houseorder],p4.[pet] [p4 pet],n4.[nationality] [n4 nationality],s4.[smoke] [s4 smoke],d4.[drink] [d4 drink]
      ,hc5.[housecolor] [hc5 housecolor],ho5.[houseorder] [ho5 houseorder],p5.[pet] [p5 pet],n5.[nationality] [n5 nationality],s5.[smoke] [s5 smoke],d5.[drink] [d5 drink]

from housecolor hc1
cross join houseorder ho1
cross join pet p1
cross join nationality n1
cross join smoke s1
cross join drink d1

cross join housecolor hc2
cross join houseorder ho2
cross join pet p2
cross join nationality n2
cross join smoke s2
cross join drink d2

cross join housecolor hc3
cross join houseorder ho3
cross join pet p3
cross join nationality n3
cross join smoke s3
cross join drink d3

cross join housecolor hc4
cross join houseorder ho4
cross join pet p4
cross join nationality n4
cross join smoke s4
cross join drink d4

cross join housecolor hc5
cross join houseorder ho5
cross join pet p5
cross join nationality n5
cross join smoke s5
cross join drink d5

       -- each feature is mutually exclusive (a feature can only be attributed to one person) so eliminate all but unique combinations here
where  hc1.[housecolor] <> hc2.[housecolor] and ho1.[houseorder] <> ho2.[houseorder] and p1.[pet] <> p2.[pet] and n1.[nationality] <> n2.[nationality] and  s1.[smoke] <> s2.[smoke] and d1.[drink] <> d2.[drink]
and    hc1.[housecolor] <> hc3.[housecolor] and ho1.[houseorder] <> ho3.[houseorder] and p1.[pet] <> p3.[pet] and n1.[nationality] <> n3.[nationality] and  s1.[smoke] <> s3.[smoke] and d1.[drink] <> d3.[drink]
and    hc1.[housecolor] <> hc4.[housecolor] and ho1.[houseorder] <> ho4.[houseorder] and p1.[pet] <> p4.[pet] and n1.[nationality] <> n4.[nationality] and  s1.[smoke] <> s4.[smoke] and d1.[drink] <> d4.[drink]
and    hc1.[housecolor] <> hc5.[housecolor] and ho1.[houseorder] <> ho5.[houseorder] and p1.[pet] <> p5.[pet] and n1.[nationality] <> n5.[nationality] and  s1.[smoke] <> s5.[smoke] and d1.[drink] <> d5.[drink]

and    hc2.[housecolor] <> hc3.[housecolor] and ho2.[houseorder] <> ho3.[houseorder] and p2.[pet] <> p3.[pet] and n2.[nationality] <> n3.[nationality] and  s2.[smoke] <> s3.[smoke] and d2.[drink] <> d3.[drink]
and    hc2.[housecolor] <> hc4.[housecolor] and ho2.[houseorder] <> ho4.[houseorder] and p2.[pet] <> p4.[pet] and n2.[nationality] <> n4.[nationality] and  s2.[smoke] <> s4.[smoke] and d2.[drink] <> d4.[drink]
and    hc2.[housecolor] <> hc5.[housecolor] and ho2.[houseorder] <> ho5.[houseorder] and p2.[pet] <> p5.[pet] and n2.[nationality] <> n5.[nationality] and  s2.[smoke] <> s5.[smoke] and d2.[drink] <> d5.[drink]

and    hc3.[housecolor] <> hc4.[housecolor] and ho3.[houseorder] <> ho4.[houseorder] and p3.[pet] <> p4.[pet] and n3.[nationality] <> n4.[nationality] and  s3.[smoke] <> s4.[smoke] and d3.[drink] <> d4.[drink]
and    hc3.[housecolor] <> hc5.[housecolor] and ho3.[houseorder] <> ho5.[houseorder] and p3.[pet] <> p5.[pet] and n3.[nationality] <> n5.[nationality] and  s3.[smoke] <> s5.[smoke] and d3.[drink] <> d5.[drink]

and    hc4.[housecolor] <> hc5.[housecolor] and ho4.[houseorder] <> ho5.[houseorder] and p4.[pet] <> p5.[pet] and n4.[nationality] <> n5.[nationality] and  s4.[smoke] <> s5.[smoke] and d4.[drink] <> d5.[drink]

       -- apply logic rules
and   (-- rule 1
      (hc1.housecolor='red' and n1.nationality ='brit')
or    (hc2.housecolor='red' and n2.nationality ='brit')
or    (hc3.housecolor='red' and n3.nationality ='brit')
or    (hc4.housecolor='red' and n4.nationality ='brit')
or    (hc5.housecolor='red' and n5.nationality ='brit')
)      -- end rule 1

and   (-- rule 2
      (n1.nationality='swede' and p1.pet ='dog')        
or    (n2.nationality='swede' and p2.pet ='dog') 
or    (n3.nationality='swede' and p3.pet ='dog') 
or    (n4.nationality='swede' and p4.pet ='dog') 
or    (n5.nationality='swede' and p5.pet ='dog') 
)      -- end rule 2

and   (-- rule 3
      (n1.nationality='dane' and d1.drink ='tea')      
or    (n2.nationality='dane' and d2.drink ='tea')     
or    (n3.nationality='dane' and d3.drink ='tea')  
or    (n4.nationality='dane' and d4.drink ='tea')  
or    (n5.nationality='dane' and d5.drink ='tea')  
)      -- end rule 3

and   (-- rule 4
      (hc1.housecolor='green' and ho1.houseorder = ho2.houseorder-1 and hc2.housecolor='white') 
or    (hc1.housecolor='green' and ho1.houseorder = ho3.houseorder-1 and hc3.housecolor='white') 
or    (hc1.housecolor='green' and ho1.houseorder = ho4.houseorder-1 and hc4.housecolor='white') 
or    (hc1.housecolor='green' and ho1.houseorder = ho5.houseorder-1 and hc5.housecolor='white') 

or    (hc2.housecolor='green' and ho2.houseorder = ho1.houseorder-1 and hc1.housecolor='white') 
or    (hc2.housecolor='green' and ho2.houseorder = ho3.houseorder-1 and hc3.housecolor='white') 
or    (hc2.housecolor='green' and ho2.houseorder = ho4.houseorder-1 and hc4.housecolor='white') 
or    (hc2.housecolor='green' and ho2.houseorder = ho5.houseorder-1 and hc5.housecolor='white') 

or    (hc3.housecolor='green' and ho3.houseorder = ho1.houseorder-1 and hc1.housecolor='white') 
or    (hc3.housecolor='green' and ho3.houseorder = ho2.houseorder-1 and hc2.housecolor='white') 
or    (hc3.housecolor='green' and ho3.houseorder = ho4.houseorder-1 and hc4.housecolor='white') 
or    (hc3.housecolor='green' and ho3.houseorder = ho5.houseorder-1 and hc5.housecolor='white') 

or    (hc4.housecolor='green' and ho4.houseorder = ho1.houseorder-1 and hc1.housecolor='white') 
or    (hc4.housecolor='green' and ho4.houseorder = ho2.houseorder-1 and hc2.housecolor='white') 
or    (hc4.housecolor='green' and ho4.houseorder = ho3.houseorder-1 and hc3.housecolor='white') 
or    (hc4.housecolor='green' and ho4.houseorder = ho5.houseorder-1 and hc5.housecolor='white') 
)      -- end rule 4

and   (--rule 5
      (hc1.housecolor='green' and d1.drink ='coffee')    
or    (hc2.housecolor='green' and d2.drink ='coffee')    
or    (hc3.housecolor='green' and d3.drink ='coffee')   
or    (hc4.housecolor='green' and d4.drink ='coffee')  
or    (hc5.housecolor='green' and d5.drink ='coffee')  
)      -- end rule 5 

and   (-- rule 6
      (s1.smoke='pall mall' and p1.pet ='bird')           
or    (s2.smoke='pall mall' and p2.pet ='bird')  
or    (s3.smoke='pall mall' and p3.pet ='bird')    
or    (s4.smoke='pall mall' and p4.pet ='bird') 
or    (s5.smoke='pall mall' and p5.pet ='bird') 
)      -- end rule 6 

and   (-- rule 7
      (hc1.housecolor='yellow' and s1.smoke ='dunhill') 
or    (hc2.housecolor='yellow' and s2.smoke ='dunhill')    
or    (hc3.housecolor='yellow' and s3.smoke ='dunhill')     
or    (hc4.housecolor='yellow' and s4.smoke ='dunhill')     
or    (hc5.housecolor='yellow' and s5.smoke ='dunhill')  
)      -- end rule 7 

and   (-- rule 8
      (ho1.houseorder=3 and d1.drink ='milk') 
or    (ho2.houseorder=3 and d2.drink ='milk')   
or    (ho3.houseorder=3 and d3.drink ='milk')    
or    (ho4.houseorder=3 and d4.drink ='milk')   
or    (ho5.houseorder=3 and d5.drink ='milk')           
)      -- end rule 8

and   (-- rule 9
      (n1.nationality='norwegian' and ho1.houseorder = 1) 
or    (n2.nationality='norwegian' and ho2.houseorder = 1)      
or    (n3.nationality='norwegian' and ho3.houseorder = 1)  
or    (n4.nationality='norwegian' and ho4.houseorder = 1) 
or    (n5.nationality='norwegian' and ho5.houseorder = 1) 
)      -- end rule 9

and   (-- rule 10
      (s1.smoke='blends' and abs(ho1.houseorder - ho2.houseorder) = 1 and p2.pet ='cat') 
or    (s1.smoke='blends' and abs(ho1.houseorder - ho3.houseorder) = 1 and p3.pet ='cat') 
or    (s1.smoke='blends' and abs(ho1.houseorder - ho4.houseorder) = 1 and p4.pet ='cat') 
or    (s1.smoke='blends' and abs(ho1.houseorder - ho5.houseorder) = 1 and p5.pet ='cat') 

or    (s2.smoke='blends' and abs(ho2.houseorder - ho1.houseorder) = 1 and p1.pet ='cat') 
or    (s2.smoke='blends' and abs(ho2.houseorder - ho3.houseorder) = 1 and p3.pet ='cat') 
or    (s2.smoke='blends' and abs(ho2.houseorder - ho4.houseorder) = 1 and p4.pet ='cat') 
or    (s2.smoke='blends' and abs(ho2.houseorder - ho5.houseorder) = 1 and p5.pet ='cat') 

or    (s3.smoke='blends' and abs(ho3.houseorder - ho1.houseorder) = 1 and p1.pet ='cat') 
or    (s3.smoke='blends' and abs(ho3.houseorder - ho2.houseorder) = 1 and p2.pet ='cat') 
or    (s3.smoke='blends' and abs(ho3.houseorder - ho4.houseorder) = 1 and p4.pet ='cat') 
or    (s3.smoke='blends' and abs(ho3.houseorder - ho5.houseorder) = 1 and p5.pet ='cat') 

or    (s4.smoke='blends' and abs(ho4.houseorder - ho1.houseorder) = 1 and p1.pet ='cat')
or    (s4.smoke='blends' and abs(ho4.houseorder - ho2.houseorder) = 1 and p2.pet ='cat')
or    (s4.smoke='blends' and abs(ho4.houseorder - ho3.houseorder) = 1 and p3.pet ='cat')
or    (s4.smoke='blends' and abs(ho4.houseorder - ho5.houseorder) = 1 and p5.pet ='cat') 
)      -- end rule 10

and   (-- rule 11
      (s1.smoke='dunhill' and abs(ho1.houseorder - ho2.houseorder) = 1 and p2.pet ='horse') 
or    (s1.smoke='dunhill' and abs(ho1.houseorder - ho3.houseorder) = 1 and p3.pet ='horse') 
or    (s1.smoke='dunhill' and abs(ho1.houseorder - ho4.houseorder) = 1 and p4.pet ='horse') 
or    (s1.smoke='dunhill' and abs(ho1.houseorder - ho5.houseorder) = 1 and p5.pet ='horse') 

or    (s2.smoke='dunhill' and abs(ho2.houseorder - ho1.houseorder) = 1 and p1.pet ='horse') 
or    (s2.smoke='dunhill' and abs(ho2.houseorder - ho3.houseorder) = 1 and p3.pet ='horse') 
or    (s2.smoke='dunhill' and abs(ho2.houseorder - ho4.houseorder) = 1 and p4.pet ='horse') 
or    (s2.smoke='dunhill' and abs(ho2.houseorder - ho5.houseorder) = 1 and p5.pet ='horse') 

or    (s3.smoke='dunhill' and abs(ho3.houseorder - ho1.houseorder) = 1 and p1.pet ='horse') 
or    (s3.smoke='dunhill' and abs(ho3.houseorder - ho2.houseorder) = 1 and p2.pet ='horse') 
or    (s3.smoke='dunhill' and abs(ho3.houseorder - ho4.houseorder) = 1 and p4.pet ='horse') 
or    (s3.smoke='dunhill' and abs(ho3.houseorder - ho5.houseorder) = 1 and p5.pet ='horse') 

or    (s4.smoke='dunhill' and abs(ho4.houseorder - ho1.houseorder) = 1 and p1.pet ='horse') 
or    (s4.smoke='dunhill' and abs(ho4.houseorder - ho2.houseorder) = 1 and p2.pet ='horse') 
or    (s4.smoke='dunhill' and abs(ho4.houseorder - ho3.houseorder) = 1 and p3.pet ='horse') 
or    (s4.smoke='dunhill' and abs(ho4.houseorder - ho5.houseorder) = 1 and p5.pet ='horse') 
)      -- end rule 11

and   (-- rule 12
      (s1.smoke='bluemasters' and d1.drink ='beer') 
or    (s2.smoke='bluemasters' and d2.drink ='beer') 
or    (s3.smoke='bluemasters' and d3.drink ='beer') 
or    (s4.smoke='bluemasters' and d4.drink ='beer') 
or    (s5.smoke='bluemasters' and d5.drink ='beer') 
)      -- end rule 12

and   (-- rule 13 
      (n1.nationality='german' and s1.smoke ='prince') 
or    (n2.nationality='german' and s2.smoke ='prince') 
or    (n3.nationality='german' and s3.smoke ='prince') 
or    (n4.nationality='german' and s4.smoke ='prince') 
or    (n5.nationality='german' and s5.smoke ='prince') 
)      -- end rule 13 

and   (-- rule 14
      (n1.nationality='norwegian' and abs(ho1.houseorder - ho2.houseorder) = 1 and hc2.housecolor = 'blue') 
or    (n1.nationality='norwegian' and abs(ho1.houseorder - ho3.houseorder) = 1 and hc3.housecolor = 'blue') 
or    (n1.nationality='norwegian' and abs(ho1.houseorder - ho4.houseorder) = 1 and hc4.housecolor = 'blue') 
or    (n1.nationality='norwegian' and abs(ho1.houseorder - ho5.houseorder) = 1 and hc5.housecolor = 'blue') 

or    (n2.nationality='norwegian' and abs(ho2.houseorder - ho1.houseorder) = 1 and hc1.housecolor = 'blue') 
or    (n2.nationality='norwegian' and abs(ho2.houseorder - ho3.houseorder) = 1 and hc3.housecolor = 'blue') 
or    (n2.nationality='norwegian' and abs(ho2.houseorder - ho4.houseorder) = 1 and hc4.housecolor = 'blue') 
or    (n2.nationality='norwegian' and abs(ho2.houseorder - ho5.houseorder) = 1 and hc5.housecolor = 'blue') 

or    (n3.nationality='norwegian' and abs(ho3.houseorder - ho1.houseorder) = 1 and hc1.housecolor = 'blue') 
or    (n3.nationality='norwegian' and abs(ho3.houseorder - ho2.houseorder) = 1 and hc2.housecolor = 'blue') 
or    (n3.nationality='norwegian' and abs(ho3.houseorder - ho4.houseorder) = 1 and hc4.housecolor = 'blue') 
or    (n3.nationality='norwegian' and abs(ho3.houseorder - ho5.houseorder) = 1 and hc5.housecolor = 'blue') 

or    (n4.nationality='norwegian' and abs(ho4.houseorder - ho1.houseorder) = 1 and hc1.housecolor = 'blue') 
or    (n4.nationality='norwegian' and abs(ho4.houseorder - ho2.houseorder) = 1 and hc2.housecolor = 'blue') 
or    (n4.nationality='norwegian' and abs(ho4.houseorder - ho3.houseorder) = 1 and hc3.housecolor = 'blue') 
or    (n4.nationality='norwegian' and abs(ho4.houseorder - ho5.houseorder) = 1 and hc5.housecolor = 'blue') 
)      -- end rule 14

and   (-- rule 15
      (s1.smoke='blends' and abs(ho1.houseorder - ho2.houseorder) = 1 and hc2.housecolor = 'water') 
or    (s1.smoke='blends' and abs(ho1.houseorder - ho3.houseorder) = 1 and hc3.housecolor = 'water') 
or    (s1.smoke='blends' and abs(ho1.houseorder - ho4.houseorder) = 1 and hc4.housecolor = 'water') 
or    (s1.smoke='blends' and abs(ho1.houseorder - ho5.houseorder) = 1 and hc5.housecolor = 'water') 

or    (s2.smoke='blends' and abs(ho2.houseorder - ho1.houseorder) = 1 and d1.drink = 'water') 
or    (s2.smoke='blends' and abs(ho2.houseorder - ho3.houseorder) = 1 and d3.drink = 'water') 
or    (s2.smoke='blends' and abs(ho2.houseorder - ho4.houseorder) = 1 and d4.drink = 'water') 
or    (s2.smoke='blends' and abs(ho2.houseorder - ho5.houseorder) = 1 and d5.drink = 'water') 

or    (s3.smoke='blends' and abs(ho3.houseorder - ho1.houseorder) = 1 and d1.drink = 'water') 
or    (s3.smoke='blends' and abs(ho3.houseorder - ho2.houseorder) = 1 and d2.drink = 'water') 
or    (s3.smoke='blends' and abs(ho3.houseorder - ho4.houseorder) = 1 and d4.drink = 'water') 
or    (s3.smoke='blends' and abs(ho3.houseorder - ho5.houseorder) = 1 and d5.drink = 'water') 

or    (s4.smoke='blends' and abs(ho4.houseorder - ho1.houseorder) = 1 and d1.drink = 'water') 
or    (s4.smoke='blends' and abs(ho4.houseorder - ho2.houseorder) = 1 and d2.drink = 'water') 
or    (s4.smoke='blends' and abs(ho4.houseorder - ho3.houseorder) = 1 and d3.drink = 'water') 
or    (s4.smoke='blends' and abs(ho4.houseorder - ho5.houseorder) = 1 and d5.drink = 'water') 
)      -- end rule 15 

and ho1.houseorder = 1 
and ho2.houseorder = 2 
and ho3.houseorder = 3
and ho4.houseorder = 4
and ho5.houseorder = 5 
more ▼

answered Aug 31, 2011 at 09:21 PM

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

I don't have the head right now to start solving this, I think your suggested solution seems quite reasonable. Lots of code, but that's because there's a lot of logic to it.. One thing that speeds up things quite a bit is to materialize the table houseorder, of course with column houseorder being the clustered index. I haven't tested the other ones, but I can't imagine materializing the strings would speed anything up - they aren't ever sequencially compared.
Sep 01, 2011 at 02:21 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

Not an answer as such, yet.

I'm working on an approach using the sieve method - create a temporary table with all possible combinations, and then delete where possible.

Just using the basic "the guy in the blue house smokes weed"-type rules, you can get it down from 15625 rows to just over 100.

Now all I have to do is work out the bits for the relative rules - "x lives next to y" stuff.
more ▼

answered Sep 01, 2011 at 03:09 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

My head is spinning!
Sep 01, 2011 at 05:47 AM sp_lock
So's mine. I've taken a break from it to do some "real" work for a while...
Sep 01, 2011 at 06:57 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

Ok, so I've started out with @Scot Hauder 's solution and removed the houseorder table. Since there's already numbering (hc1, d1, n1 etc) there's no need for a table to keep track of order.

I created the tables instead of using the CTE, to speed things up - on my PC it helped with a factor 10.

So here it is:

create table housecolor(housecolor varchar(10) primary key)
create table pet(pet varchar(10) primary key)
create table nationality(nationality varchar(10) primary key)
create table smoke(smoke varchar(11) primary key)
create table drink (drink varchar(10) primary key)
insert into housecolor(housecolor)     select 'red' [housecolor] union all select 'green' union all select 'yellow' union all select 'blue' union all select 'white'
insert into pet(pet)
    select 'FISH' [pet] union all select 'dog' union all select 'cat' union all select 'bird' union all select 'horse'
insert into nationality(nationality)
    select 'swede' [nationality] union all select 'norwegian' union all select 'german' union all select 'dane' union all select 'brit'
insert into smoke(smoke)
    select 'pall mall' [smoke] union all select 'blends' union all select 'dunhill' union all select 'bluemasters' union all select 'prince'
insert into drink(drink)
    select 'tea' [drink] union all select 'coffee' union all select 'milk' union all select 'beer' union all select 'water'

select hc1.[housecolor] [hc1 housecolor],p1.[pet] [p1 pet],n1.[nationality] [n1 nationality],s1.[smoke] [s1 smoke],d1.[drink] [d1 drink]
      ,hc2.[housecolor] [hc2 housecolor],p2.[pet] [p2 pet],n2.[nationality] [n2 nationality],s2.[smoke] [s2 smoke],d2.[drink] [d2 drink]
      ,hc3.[housecolor] [hc3 housecolor],p3.[pet] [p3 pet],n3.[nationality] [n3 nationality],s3.[smoke] [s3 smoke],d3.[drink] [d3 drink]
      ,hc4.[housecolor] [hc4 housecolor],p4.[pet] [p4 pet],n4.[nationality] [n4 nationality],s4.[smoke] [s4 smoke],d4.[drink] [d4 drink]
      ,hc5.[housecolor] [hc5 housecolor],p5.[pet] [p5 pet],n5.[nationality] [n5 nationality],s5.[smoke] [s5 smoke],d5.[drink] [d5 drink]

from housecolor hc1
cross join pet p1
cross join nationality n1
cross join smoke s1
cross join drink d1

cross join housecolor hc2
cross join pet p2
cross join nationality n2
cross join smoke s2
cross join drink d2

cross join housecolor hc3
cross join pet p3
cross join nationality n3
cross join smoke s3
cross join drink d3

cross join housecolor hc4
cross join pet p4
cross join nationality n4
cross join smoke s4
cross join drink d4

cross join housecolor hc5
cross join pet p5
cross join nationality n5
cross join smoke s5
cross join drink d5

       -- each feature is mutually exclusive (a feature can only be attributed to one person) so eliminate all but unique combinations here
where  hc1.[housecolor] <> hc2.[housecolor] and  p1.[pet] <> p2.[pet] and n1.[nationality] <> n2.[nationality] and  s1.[smoke] <> s2.[smoke] and d1.[drink] <> d2.[drink]
and    hc1.[housecolor] <> hc3.[housecolor] and  p1.[pet] <> p3.[pet] and n1.[nationality] <> n3.[nationality] and  s1.[smoke] <> s3.[smoke] and d1.[drink] <> d3.[drink]
and    hc1.[housecolor] <> hc4.[housecolor] and  p1.[pet] <> p4.[pet] and n1.[nationality] <> n4.[nationality] and  s1.[smoke] <> s4.[smoke] and d1.[drink] <> d4.[drink]
and    hc1.[housecolor] <> hc5.[housecolor] and  p1.[pet] <> p5.[pet] and n1.[nationality] <> n5.[nationality] and  s1.[smoke] <> s5.[smoke] and d1.[drink] <> d5.[drink]

and    hc2.[housecolor] <> hc3.[housecolor] and  p2.[pet] <> p3.[pet] and n2.[nationality] <> n3.[nationality] and  s2.[smoke] <> s3.[smoke] and d2.[drink] <> d3.[drink]
and    hc2.[housecolor] <> hc4.[housecolor] and  p2.[pet] <> p4.[pet] and n2.[nationality] <> n4.[nationality] and  s2.[smoke] <> s4.[smoke] and d2.[drink] <> d4.[drink]
and    hc2.[housecolor] <> hc5.[housecolor] and  p2.[pet] <> p5.[pet] and n2.[nationality] <> n5.[nationality] and  s2.[smoke] <> s5.[smoke] and d2.[drink] <> d5.[drink]

and    hc3.[housecolor] <> hc4.[housecolor] and  p3.[pet] <> p4.[pet] and n3.[nationality] <> n4.[nationality] and  s3.[smoke] <> s4.[smoke] and d3.[drink] <> d4.[drink]
and    hc3.[housecolor] <> hc5.[housecolor] and  p3.[pet] <> p5.[pet] and n3.[nationality] <> n5.[nationality] and  s3.[smoke] <> s5.[smoke] and d3.[drink] <> d5.[drink]

and    hc4.[housecolor] <> hc5.[housecolor] and  p4.[pet] <> p5.[pet] and n4.[nationality] <> n5.[nationality] and  s4.[smoke] <> s5.[smoke] and d4.[drink] <> d5.[drink]

       -- apply logic rules
and   (-- rule 1
      (hc1.housecolor='red' and n1.nationality ='brit')
or    (hc2.housecolor='red' and n2.nationality ='brit')
or    (hc3.housecolor='red' and n3.nationality ='brit')
or    (hc4.housecolor='red' and n4.nationality ='brit')
or    (hc5.housecolor='red' and n5.nationality ='brit')
)      -- end rule 1

and   (-- rule 2
      (n1.nationality='swede' and p1.pet ='dog')        
or    (n2.nationality='swede' and p2.pet ='dog') 
or    (n3.nationality='swede' and p3.pet ='dog') 
or    (n4.nationality='swede' and p4.pet ='dog') 
or    (n5.nationality='swede' and p5.pet ='dog') 
)      -- end rule 2

and   (-- rule 3
      (n1.nationality='dane' and d1.drink ='tea')      
or    (n2.nationality='dane' and d2.drink ='tea')     
or    (n3.nationality='dane' and d3.drink ='tea')  
or    (n4.nationality='dane' and d4.drink ='tea')  
or    (n5.nationality='dane' and d5.drink ='tea')  
)      -- end rule 3

and   (-- rule 4
      (hc1.housecolor='green' and hc2.housecolor='white') 
or    (hc2.housecolor='green' and hc3.housecolor='white') 
or    (hc3.housecolor='green' and hc4.housecolor='white') 
or    (hc4.housecolor='green' and hc5.housecolor='white') 
)      -- end rule 4

and   (--rule 5
      (hc1.housecolor='green' and d1.drink ='coffee')    
or    (hc2.housecolor='green' and d2.drink ='coffee')    
or    (hc3.housecolor='green' and d3.drink ='coffee')   
or    (hc4.housecolor='green' and d4.drink ='coffee')  
or    (hc5.housecolor='green' and d5.drink ='coffee')  
)      -- end rule 5

and   (-- rule 6
      (s1.smoke='pall mall' and p1.pet ='bird')           
or    (s2.smoke='pall mall' and p2.pet ='bird')  
or    (s3.smoke='pall mall' and p3.pet ='bird')    
or    (s4.smoke='pall mall' and p4.pet ='bird') 
or    (s5.smoke='pall mall' and p5.pet ='bird') 
)      -- end rule 6

and   (-- rule 7
      (hc1.housecolor='yellow' and s1.smoke ='dunhill') 
or    (hc2.housecolor='yellow' and s2.smoke ='dunhill')    
or    (hc3.housecolor='yellow' and s3.smoke ='dunhill')     
or    (hc4.housecolor='yellow' and s4.smoke ='dunhill')     
or    (hc5.housecolor='yellow' and s5.smoke ='dunhill')  
)      -- end rule 7

and   (-- rule 8
      (d3.drink ='milk') 
)      -- end rule 8

and   (-- rule 9
      (n1.nationality='norwegian') 
)      -- end rule 9

and   (-- rule 10
      (s1.smoke='blends' and p2.pet ='cat') 
or    (s2.smoke='blends' and p1.pet ='cat') 
or    (s2.smoke='blends' and p3.pet ='cat') 
or    (s3.smoke='blends' and p2.pet ='cat') 
or    (s3.smoke='blends' and p4.pet ='cat') 
or    (s4.smoke='blends' and p3.pet ='cat')
or    (s4.smoke='blends' and p5.pet ='cat') 
or    (s5.smoke='blends' and p4.pet ='cat') 
)      -- end rule 10

and   (-- rule 11
      (s1.smoke='dunhill'  and p2.pet ='horse') 
or    (s2.smoke='dunhill'  and p1.pet ='horse') 
or    (s2.smoke='dunhill'  and p3.pet ='horse') 
or    (s3.smoke='dunhill'  and p2.pet ='horse') 
or    (s3.smoke='dunhill'  and p4.pet ='horse') 
or    (s4.smoke='dunhill'  and p3.pet ='horse') 
or    (s4.smoke='dunhill'  and p5.pet ='horse') 
or    (s5.smoke='dunhill'  and p4.pet ='horse') 

)      -- end rule 11

and   (-- rule 12
      (s1.smoke='bluemasters' and d1.drink ='beer') 
or    (s2.smoke='bluemasters' and d2.drink ='beer') 
or    (s3.smoke='bluemasters' and d3.drink ='beer') 
or    (s4.smoke='bluemasters' and d4.drink ='beer') 
or    (s5.smoke='bluemasters' and d5.drink ='beer') 
)      -- end rule 12

and   (-- rule 13 
      (n1.nationality='german' and s1.smoke ='prince') 
or    (n2.nationality='german' and s2.smoke ='prince') 
or    (n3.nationality='german' and s3.smoke ='prince') 
or    (n4.nationality='german' and s4.smoke ='prince') 
or    (n5.nationality='german' and s5.smoke ='prince') 
)      -- end rule 13

and   (-- rule 14
      (n1.nationality='norwegian' and hc2.housecolor = 'blue') 
or    (n2.nationality='norwegian' and hc1.housecolor = 'blue') 
or    (n2.nationality='norwegian' and hc3.housecolor = 'blue') 
or    (n3.nationality='norwegian' and hc2.housecolor = 'blue') 
or    (n3.nationality='norwegian' and hc4.housecolor = 'blue') 
or    (n4.nationality='norwegian' and hc3.housecolor = 'blue') 
or    (n4.nationality='norwegian' and hc5.housecolor = 'blue') 
or    (n5.nationality='norwegian' and hc4.housecolor = 'blue') 
)      -- end rule 14

and   (-- rule 15
      (s1.smoke='blends' and d2.drink='water') 
or    (s2.smoke='blends' and d1.drink = 'water') 
or    (s2.smoke='blends' and d3.drink = 'water') 
or    (s3.smoke='blends' and d2.drink = 'water') 
or    (s3.smoke='blends' and d4.drink = 'water') 
or    (s4.smoke='blends' and d3.drink = 'water') 
or    (s4.smoke='blends' and d5.drink = 'water') 
or    (s5.smoke='blends' and d4.drink = 'water') 
)      -- end rule 15
more ▼

answered Sep 01, 2011 at 07:58 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x990
x11

asked: Aug 31, 2011 at 09:18 PM

Seen: 2026 times

Last Updated: Aug 31, 2011 at 09:18 PM