SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Who Has The Monitor?


Who Has The Monitor?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62248 Visits: 19102
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/whohasthemonitor.asp

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10957 Visits: 11985

Hi Steve,

Some clues allow more than one interpretation:

"The green office was to the left of the white office." - is that directly to the left (adjacent) or somewhere to the left?

"Sue has the first office." - is that the leftmost or the rightmost office?

I've made the assumption that the green and left office need not be directly adjacent, and the the first office is the leftmost office. With those assumptions, there are a total of six solutions to the problem. In two, Sue has the Dual 19" LCDs. Andy gets them in three, and the last solution has Brian working the dual 19" LCDs.

Of course, I didn't work this out for mysself. This is a SQL Server forum, after all!!

CREATE VIEW Carthesian
AS
SELECT
*
FROM (SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5) AS Offices(Office)
CROSS JOIN (SELECT 'Brian' UNION ALL
SELECT 'Steve' UNION ALL
SELECT 'Andy' UNION ALL
SELECT 'Sue' UNION ALL
SELECT 'Kevin') AS Persons(Person)
CROSS
JOIN (SELECT 'red' UNION ALL
SELECT 'green' UNION ALL
SELECT 'white' UNION ALL
SELECT 'yellow' UNION ALL
SELECT 'blue') AS Colors(Color)
CROSS
JOIN (SELECT 'Prius' UNION ALL
SELECT 'Accord' UNION ALL
SELECT 'Caravan' UNION ALL
SELECT 'Corvette' UNION ALL
SELECT 'Suburban') AS Cars(Car)
CROSS
JOIN (SELECT 'iced tea' UNION ALL
SELECT 'coffee' UNION ALL
SELECT 'Red Bull' UNION ALL
SELECT 'beer' UNION ALL
SELECT 'Mountain Dew') AS Drinks(Drink)
CROSS
JOIN (SELECT '17" CRT' UNION ALL
SELECT '15" CRT' UNION ALL
SELECT '15" LCD' UNION ALL
SELECT '17" LCD' UNION ALL
SELECT 'dual 19" LCDs') AS Monitors(Monitor)
go
CREATE VIEW Combinations
AS
SELECT
*
FROM Carthesian
WHERE NOT (Person = 'Brian' AND Color <> 'red') -- Brian had the red office.
AND NOT (Person = 'Steve' AND Monitor <> '17" CRT') -- Steve had a 17" CRT on his desk.
AND NOT (Person = 'Andy' AND Drink <> 'iced tea') -- Andy liked to drink iced tea.
AND NOT (Color = 'green' AND Drink <> 'coffee') -- The person with the green office drinks coffee.
AND NOT (Car = 'Prius' AND Monitor <> '15" CRT') -- The person who drove a Prius had a 15" CRT.
AND NOT (Office = 3 AND Drink <> 'Red Bull') -- The person in the middle office drinks Red Bull.
AND NOT (Color = 'yellow' AND Car <> 'Accord') -- The person with the yellow office drove an Accord.
AND NOT (Office = 1 AND Person <> 'Sue') -- Sue has the first office.
AND NOT (Car = 'Corvette' AND Drink <> 'beer') -- The Corvette driver drinks beer.
AND NOT (Person = 'Sue' AND Color <> 'blue') -- Sue has the blue office.
AND NOT (Person = 'Kevin' AND Car <> 'Suburban') -- Kevin drives a Suburban.
go
CREATE VIEW Solution
AS
SELECT
c1.Office AS Office_1, c1.Person AS Person_1, c1.Color AS Color_1, c1.Car AS Car_1, c1.Drink AS Drink_1, c1.Monitor AS Monitor_1,
c2.Office AS Office_2, c2.Person AS Person_2, c2.Color AS Color_2, c2.Car AS Car_2, c2.Drink AS Drink_2, c2.Monitor AS Monitor_2,
c3.Office AS Office_3, c3.Person AS Person_3, c3.Color AS Color_3, c3.Car AS Car_3, c3.Drink AS Drink_3, c3.Monitor AS Monitor_3,
c4.Office AS Office_4, c4.Person AS Person_4, c4.Color AS Color_4, c4.Car AS Car_4, c4.Drink AS Drink_4, c4.Monitor AS Monitor_4,
c5.Office AS Office_5, c5.Person AS Person_5, c5.Color AS Color_5, c5.Car AS Car_5, c5.Drink AS Drink_5, c5.Monitor AS Monitor_5
FROM Combinations AS c1
JOIN Combinations AS c2
ON
c2.Person <> c1.Person
AND c2.Color <> c1.Color
AND c2.Car <> c1.Car
AND c2.Drink <> c1.Drink
AND c2.Monitor <> c1.Monitor
JOIN Combinations AS c3
ON c3.Person <> c1.Person
AND c3.Person <> c2.Person
AND c3.Color <> c1.Color
AND c3.Color <> c2.Color
AND c3.Car <> c1.Car
AND c3.Car <> c2.Car
AND c3.Drink <> c1.Drink
AND c3.Drink <> c2.Drink
AND c3.Monitor <> c1.Monitor
AND c3.Monitor <> c2.Monitor
JOIN Combinations AS c4
ON c4.Person <> c1.Person
AND c4.Person <> c2.Person
AND c4.Person <> c3.Person
AND c4.Color <> c1.Color
AND c4.Color <> c2.Color
AND c4.Color <> c3.Color
AND c4.Car <> c1.Car
AND c4.Car <> c2.Car
AND c4.Car <> c3.Car
AND c4.Drink <> c1.Drink
AND c4.Drink <> c2.Drink
AND c4.Drink <> c3.Drink
AND c4.Monitor <> c1.Monitor
AND c4.Monitor <> c2.Monitor
AND c4.Monitor <> c3.Monitor
JOIN Combinations AS c5
ON c5.Person <> c1.Person
AND c5.Person <> c2.Person
AND c5.Person <> c3.Person
AND c5.Person <> c4.Person
AND c5.Color <> c1.Color
AND c5.Color <> c2.Color
AND c5.Color <> c3.Color
AND c5.Color <> c4.Color
AND c5.Car <> c1.Car
AND c5.Car <> c2.Car
AND c5.Car <> c3.Car
AND c5.Car <> c4.Car
AND c5.Drink <> c1.Drink
AND c5.Drink <> c2.Drink
AND c5.Drink <> c3.Drink
AND c5.Drink <> c4.Drink
AND c5.Monitor <> c1.Monitor
AND c5.Monitor <> c2.Monitor
AND c5.Monitor <> c3.Monitor
AND c5.Monitor <> c4.Monitor
WHERE c1.Office = 1
AND c2.Office = 2
AND c3.Office = 3
AND c4.Office = 4
AND c5.Office = 5
-- The green office was to the left of the white office.
AND CASE 'white'
WHEN c1.Color THEN 'F'
WHEN c2.Color THEN CASE WHEN 'green' IN (c1.Color) THEN 'T' ELSE 'F' END
WHEN c3.Color THEN CASE WHEN 'green' IN (c1.Color, c2.Color) THEN 'T' ELSE 'F' END
WHEN c4.Color THEN CASE WHEN 'green' IN (c1.Color, c2.Color, c3.Color) THEN 'T' ELSE 'F' END
WHEN c5.Color THEN 'T'
END = 'T'
--The person who drives the Caravan has an office next to the person who has the 15" LCD.
AND CASE 'Caravan'
WHEN c1.Car THEN CASE WHEN '15" LCD' IN (c2.Monitor) THEN 'T' ELSE 'F' END
WHEN c2.Car THEN CASE WHEN '15" LCD' IN (c1.Monitor, c3.Monitor) THEN 'T' ELSE 'F' END
WHEN c3.Car THEN CASE WHEN '15" LCD' IN (c2.Monitor, c4.Monitor) THEN 'T' ELSE 'F' END
WHEN c4.Car THEN CASE WHEN '15" LCD' IN (c3.Monitor, c5.Monitor) THEN 'T' ELSE 'F' END
WHEN c5.Car THEN CASE WHEN '15" LCD' IN (c4.Monitor) THEN 'T' ELSE 'F' END
END = 'T'
--The person who has the 17" LCD sits next to the person who drives an Accord.
AND CASE '17" LCD'
WHEN c1.Monitor THEN CASE WHEN 'Accord' IN (c2.Car) THEN 'T' ELSE 'F' END
WHEN c2.Monitor THEN CASE WHEN 'Accord' IN (c1.Car, c3.Car) THEN 'T' ELSE 'F' END
WHEN c3.Monitor THEN CASE WHEN 'Accord' IN (c2.Car, c4.Car) THEN 'T' ELSE 'F' END
WHEN c4.Monitor THEN CASE WHEN 'Accord' IN (c3.Car, c5.Car) THEN 'T' ELSE 'F' END
WHEN c5.Monitor THEN CASE WHEN 'Accord' IN (c4.Car) THEN 'T' ELSE 'F' END
END = 'T'
--The person who drives a Caravan sits next to the person who drinks Mountain Dew.
AND CASE 'Caravan'
WHEN c1.Car THEN CASE WHEN 'Mountain Dew' IN (c2.Drink) THEN 'T' ELSE 'F' END
WHEN c2.Car THEN CASE WHEN 'Mountain Dew' IN (c1.Drink, c3.Drink) THEN 'T' ELSE 'F' END
WHEN c3.Car THEN CASE WHEN 'Mountain Dew' IN (c2.Drink, c4.Drink) THEN 'T' ELSE 'F' END
WHEN c4.Car THEN CASE WHEN 'Mountain Dew' IN (c3.Drink, c5.Drink) THEN 'T' ELSE 'F' END
WHEN c5.Car THEN CASE WHEN 'Mountain Dew' IN (c4.Drink) THEN 'T' ELSE 'F' END
END = 'T'
go
SELECT * FROM Solution
go
DROP VIEW Solution
DROP VIEW Combinations
DROP VIEW Carthesian
go

(I hope the formatting comes out okay....)




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10957 Visits: 11985

Okay, so my assumptions were wrong.

The green office was to the left of the white office." - is that directly to the left (adjacent) or somewhere to the left?

"Sue has the first office." - is that the leftmost or the rightmost office?

I've made the assumption that the green and left office need not be directly adjacent, and the the first office is the leftmost office. With those assumptions, there are a total of six solutions to the problem.

After changing my query to the three other possible combinations of interpretations, I found an interpretation that leaves just one single answer to the question.

If we assume the the green office is directly adjacent (to the left) if the white office, AND that the first (Sue's) office is to the far right, then my query returns just two rows - and in both rows, Andy is the lucky one who gets to play with the dual 19" LCDs.

Here's the changed part of the query:

-- The green office was to the left of the white office.
AND CASE 'white'
WHEN c5.Color THEN 'F'
WHEN c4.Color THEN CASE WHEN 'green' IN (c5.Color) THEN 'T' ELSE 'F' END
WHEN c3.Color THEN CASE WHEN 'green' IN (c4.Color) THEN 'T' ELSE 'F' END
WHEN c2.Color THEN CASE WHEN 'green' IN (c3.Color) THEN 'T' ELSE 'F' END
WHEN c1.Color THEN CASE WHEN 'green' IN (c2.Color) THEN 'T' ELSE 'F' END
END = 'T'

BTW, the execution plan is definitely, ahem, "interesting"




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
cs_troyk
cs_troyk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1694 Visits: 969

It certainly makes a difference depending on whether one assumes the "first office" means rightmost or leftmost.

I also worked this out using SQL Server (it's supposed to be a logical inference engine, after all!)

I noticed that the relations I end up with that fulfill the constraints leave open the question of which offices(positionally) certain engineers occupy, and which drinks they like.


TroyK





Datagod-309892
Datagod-309892
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 98
What a collosal waste of time. Dont you people have jobs or something?
Noel Kennedy
Noel Kennedy
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 57
I have deduced, only by using a powerful new feature in SQL 2005, Message Queue, that there are 2 solutions (at least) because sue can only drink beer or mountain dew. It is possible to construct 2 solutions based on this logic.
Antares686
Antares686
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11446 Visits: 780

I would say it has to be Andy did it in the Yellow office with the spare change in his pocket.

Howevr I am the third gunman on the grassy knoll who just happens to have dual 19s.





Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62248 Visits: 19102
, LOL, it's a pretty good puzzle.

I didn't think about the first being right. Should have clarified this a little more, but being an English speaker, tend to go left to right. The blue house is the left most one. Also I do think that I potentially need one more clue since the solution above looks ok and I think there's a place where you need to guess the ordering.

How about "The red house is in the middle".

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Noel Kennedy
Noel Kennedy
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 57
Bah! With the "The red house is in the middle" (house, here I assume means office!), my solution is now wrong. Unfortunately, my colleagues is now the correct one. Please change to make me more clever than him
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10957 Visits: 11985

Hi Steve,

Fortunately, I had kept my query window open over the day!

With the clarification that first = leftmost (my first assumption) and the additional hint that the red house (office) is in the middle, I still get three solutions with the green offfice directly left of the white office, and even four solutions with the green office "somewhere to the left" of the white office.

In both lists, the 19" Dual monitor can be in Sue's, Andy's, or Brian's office.

(Just run the SQL I included in my first post, with this additional line for the Combinations view:

AND NOT (Office = 3 AND Color <> 'red') -- The red house (office) is in the middle.




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search