Einstein's Riddle

 0 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: The Brit lives in the red house. The Swede keeps dogs as pets. The Dane drinks tea. The green house is on the immediate left of the white house. The green house's owner drinks coffee. The owner who smokes Pall Mall rears birds. The owner of the yellow house smokes Dunhill. The owner living in the center house drinks milk. The Norwegian lives in the first house. The owner who smokes Blends lives next to the one who keeps cats. The owner who keeps the horse lives next to the one who smokes Dunhill. The owner who smokes Bluemasters drinks beer. The German smokes Prince. The Norwegian lives next to the blue house. 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 '11 at 09:18 PM in Default Scot Hauder 5.9k ● 13 ● 15 ● 18 Cool, now 100% of people can solve it. Take that Einstein. :) Sep 01 '11 at 02:13 AM Fatherjack ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 ``````;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 '11 at 09:21 PM Scot Hauder 5.9k ● 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 '11 at 02:21 AM Magnus Ahlkvist add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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 '11 at 03:09 AM ThomasRushton ♦ 33.1k ● 11 ● 20 ● 44 My head is spinning! Sep 01 '11 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 '11 at 06:57 AM ThomasRushton ♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 0 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 '11 at 07:58 AM Magnus Ahlkvist 15.4k ● 15 ● 17 ● 32 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

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.

By Email:

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

Topics:

x975
x11

asked: Aug 31 '11 at 09:18 PM

Seen: 1665 times

Last Updated: Aug 31 '11 at 09:18 PM