Published
Friday, November 04, 2005 4:46 PM
/*
Todays puzzle comes from Andras who incudentally got the best
solution so far to the last puzzle.
We have two tables a letters table and a numbers table. The letters table
is a letter followed by three numbers. The idea is to write a query that
returns all the letters that have all numbers listed in the numbers table.
This is probably best explaind in an example. so the Letters table contains
a,1,2,3
a,4,5,6
a,4,5,6
b,1,2,3
c,1,2,3
c,4,5,3
d,1,9,4
d,2,8,5
d,3,7,6
d,4,6,7
e,1,2,3
e,4,5,6
e,4,5,6
e,4,6,7
The numbers table contains.
1,2,3
4,5,6
4,5,6
So for a letter to be selected it must have three entries in the letters table
One with 1,2,3. A second with 4,5,6. A third with 4,5,6. So if there is a
duplicate in the numbers table there must be a duplicate in the letters table.
In the example above the result will be a and e. A is returned as it has
exactally the three numbers following it that are in the numbers table. E is
also returned as it also has all of the entries in the numbers table and it
doesn't matter if there are extra ones.
The expected result from the data bellow should be:
Letter
------
a
e
Happy coding :)
*/
SET NOCOUNT ON
DECLARE @Letters TABLE
(
Letter Char(1) NOT NULL,
NumberA INT NOT NULL,
NumberB INT NOT NULL,
NumberC INT NOT NULL
)
DECLARE @Numbers TABLE
(
NumberA INT NOT NULL,
NumberB INT NOT NULL,
NumberC INT NOT NULL
)
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'a', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'a', 2, 3, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'a', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'a', 1, 3, 4
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'b', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'b', 2, 3, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'c', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 6, 5, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 3, 2, 4
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 7, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 1, 4, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'd', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'e', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'e', 2, 3, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'e', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'f', 1, 2, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'f', 2, 3, 8
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'f', 7, 3, 3
INSERT INTO @Letters(Letter, NumberA, NumberB, NumberC)
SELECT 'f', 1, 2, 3
INSERT INTO @Numbers(NumberA, NumberB, NumberC)
SELECT 1, 2, 3
INSERT INTO @Numbers(NumberA, NumberB, NumberC)
SELECT 1, 2, 3
INSERT INTO @Numbers(NumberA, NumberB, NumberC)
SELECT 2, 3, 3
SET NOCOUNT OFF