November 20, 2015 at 6:08 pm
I have a site in which users, who are members of a book club, are presented with two books that the club has read and they have to indicate a preference of one over the other. I am interested in finding instances of a Rock/Paper/Scissors preference paradox - for example Person A prefers Book 1 to Book 2 and Book 2 to Book 3, but prefers book 3 to book 1. These preference paradoxes could conceivably go dozens of books deep (e.g. 1>2>3>4>5>6>7>8>9>10>11>12>1). Given the table schema below, does anyone have a clever way to find these? It would be cool to get the results in the format "Book Title 1, Book Title 2, etc..." in the order in which the paradox is generated. Obviously, the results would be circular, so I'd only want one iteration through the results (picking an arbitrary starting point). Is there a set-based solution that can do this with a single query? I have this posted in SQL 2008, because that's what I'm running right now, but solutions using techniques available only in 2012 would be fine. This is more of a "challenge" than anything and I'm curious to see what kinds of ideas are out there.
In the schema below, the "Chooser" table contains the names of the people making the selections in the "Battle" table. The WinnerBookID and LoserBookID in the Battle table are BookID's in the Book table. I've stripped out all the irrelevant columns from these table definitions:
CREATE TABLE [dbo].[Chooser](
[ChooserID] [int] IDENTITY(1,1) NOT NULL,
[ChooserName] [varchar](250) NULL,
CONSTRAINT [PK_Chooser] PRIMARY KEY CLUSTERED
(
[ChooserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Book](
[BookID] [int] IDENTITY(1,1) NOT NULL,
[BookTitle] [varchar](500) NULL,
CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED
(
[BookID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Battle](
[BattleID] [int] IDENTITY(1,1) NOT NULL,
[WinnerBookID] [int] NOT NULL,
[LoserBookID] [int] NOT NULL,
[ChooserID] [int] NOT NULL,
CONSTRAINT [PK_Battle] PRIMARY KEY CLUSTERED
(
[BattleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
November 20, 2015 at 7:41 pm
This seems like a fun little exercise and this kind of thing sharpens your skills for sure. +1000.
Here's what I put together. Note that I cleaned up your DDL. See my notes, play around with and let me know if this is what you are looking for (or if I'm close).
/****************************************************************************************
(1) Setup DDL
****************************************************************************************/
IF OBJECT_ID('dbo.Chooser') IS NOT NULL DROP TABLE dbo.Chooser;
IF OBJECT_ID('dbo.Book') IS NOT NULL DROP TABLE dbo.Book;
IF OBJECT_ID('dbo.Battle') IS NOT NULL DROP TABLE dbo.Battle;
IF OBJECT_ID('dbo.BattleView') IS NOT NULL DROP VIEW dbo.BattleView;
CREATE TABLE dbo.Chooser
(
ChooserID int IDENTITY(1,1) NOT NULL,
ChooserName varchar(250) NOT NULL, -- NOT NULL is better
CONSTRAINT PK_Chooser PRIMARY KEY CLUSTERED (ChooserID)
);
CREATE TABLE dbo.Book
(
BookID int IDENTITY(1,1) NOT NULL,
BookTitle varchar(500) NOT NULL, -- NOT NULL is better
CONSTRAINT PK_Book PRIMARY KEY CLUSTERED (BookID)
);
CREATE TABLE dbo.Battle
(
BattleID int IDENTITY(1,1) NOT NULL,
WinnerBookID int NOT NULL,
LoserBookID int NOT NULL,
ChooserID int NOT NULL,
CONSTRAINT PK_Battle PRIMARY KEY CLUSTERED (BattleID)
);
GO
CREATE VIEW dbo.BattleView AS
SELECT
BattleID,
c.ChooserID,
c.ChooserName,
WinnerBookTitle = winner.BookTitle,
LoserBookTitle = loser.BookTitle
FROM dbo.Battle b
JOIN dbo.Chooser c ON b.ChooserID = c.ChooserID
JOIN dbo.Book winner ON b.WinnerBookID = winner.BookID
JOIN dbo.Book loser ON b.LoserBookID = loser.BookID;
GO
/****************************************************************************************
(2) Populate Chooser and Book with 3 choosers and six books
****************************************************************************************/
INSERT dbo.Chooser(ChooserName)
SELECT Chooser
FROM (VALUES ('Bill'),('Sue'),('Larry'))x(chooser); -- create 3 sample users (with weird names)
INSERT dbo.Book(BookTitle)
SELECT book
FROM (VALUES('Hadoop'),('NoSQL'),('T-SQL'),('SSIS'),('Oracle'),('PL/SQL')) x(book);
/****************************************************************************************
(3) Create a winner and loser book for all choosers
This will create every possible chooser/winner/loser combination
with 6 books there are 15 possible combinations of books that are not equal.
We'll have 15 book combinations for each user, 45 total records
****************************************************************************************/
WITH Randomizer AS
(
SELECT
WinnerBookID = b1.BookID,
LoserBookID = b2.BookID,
c.ChooserID,
RandPref = ABS(CHECKSUM(newid())%2)
FROM dbo.Book b1
CROSS JOIN dbo.Book b2 -- create a book cartesian product
CROSS JOIN dbo.Chooser c
WHERE b1.BookID < b2.BookID -- this will make the book combinations unique
)
INSERT dbo.Battle
SELECT
WinnerBookID = CASE RandPref WHEN 1 THEN WinnerBookID ELSE LoserBookID END,
LoserBookID = CASE RandPref WHEN 1 THEN LoserBookID ELSE WinnerBookID END,
ChooserID
FROM Randomizer
ORDER BY ChooserID, WinnerBookID;
/*
Note that the ORDER BY is not needed and would hurt performance
I just added it to make the sample data easier to sift through;
*/
/****************************************************************************************
(5) How we would do a battle:
****************************************************************************************/
-- for fun, here's a random Chooser
DECLARE @Chooser int;
SELECT TOP(1) @Chooser=ChooserID FROM dbo.Chooser ORDER BY newid() --picks random chooser
SELECT TOP (1) * FROM dbo.BattleView WHERE ChooserID = @Chooser ORDER BY newid();
-- ^picks a random book for @chooser
-- Itzik Ben-Gan 2001
November 20, 2015 at 9:06 pm
Hi Alan,
This does a really great job of generating random test data. What I'm looking for is a query that pulls out the data from the Battle table that indicates circular preference. For instance this query will pull 3 records for every case where we have a circular preference with n=3 (n being the length of the "chain" of circular preferences) = I'm using your view here 😀
SELECT
bv1.ChooserName, bv1.WinnerBookTitle, bv2.WinnerBookTitle, bv3.WinnerBookTitle
FROM BattleView bv1
INNER JOIN BattleView bv2
ON bv1.LoserBookTitle=bv2.WinnerBookTitle
AND bv1.ChooserID=bv2.ChooserID
INNER JOIN BattleView bv3
ON bv2.LoserBookTitle=bv3.WinnerBookTitle
AND bv1.WinnerBookTitle=bv3.LoserBookTitle
AND bv1.ChooserID=bv3.ChooserID
Obviously, I could keep adding joins for a larger n (for n=4, it would be 4 results per circular preference "chain"). I would really like to get a single SELECT query (CTE's are acceptable) that generates the results for any n in a comma delimited list for a singular circular preference "chain". For example, given a Battle table and the Chooser and Books you've created:
BattleID WinnerBookID LoserBookID ChooserID
----------- ------------ ----------- -----------
1 1 2 1
6 2 4 1
8 4 5 1
12 5 2 1
13 5 1 1
Return:
ChooserName CircularPreference
----------- ---------------------------
Bill Oracle, Hadoop, SSIS
Bill Oracle, Hadoop, NoSQL, SSIS
November 21, 2015 at 5:05 pm
jmpatchak (11/20/2015)
Hi Alan,This does a really great job of generating random test data. What I'm looking for is a query that pulls out the data from the Battle table that indicates circular preference. For instance this query will pull 3 records for every case where we have a circular preference with n=3 (n being the length of the "chain" of circular preferences) = I'm using your view here 😀
SELECT
bv1.ChooserName, bv1.WinnerBookTitle, bv2.WinnerBookTitle, bv3.WinnerBookTitle
FROM BattleView bv1
INNER JOIN BattleView bv2
ON bv1.LoserBookTitle=bv2.WinnerBookTitle
AND bv1.ChooserID=bv2.ChooserID
INNER JOIN BattleView bv3
ON bv2.LoserBookTitle=bv3.WinnerBookTitle
AND bv1.WinnerBookTitle=bv3.LoserBookTitle
AND bv1.ChooserID=bv3.ChooserID
Obviously, I could keep adding joins for a larger n (for n=4, it would be 20 results per circular preference "chain"). I would really like to get a single SELECT query (CTE's are acceptable) that generates the results for any n in a comma delimited list for a singular circular preference "chain". For example, given a Battle table and the Chooser and Books you've created:
BattleID WinnerBookID LoserBookID ChooserID
----------- ------------ ----------- -----------
1 1 2 1
6 2 4 1
8 4 5 1
12 5 2 1
13 5 1 1
Return:
ChooserName CircularPreference
----------- ---------------------------
Bill Oracle, Hadoop, SSIS
Bill Oracle, Hadoop, NoSQL, SSIS
So put the data in a readily consumable form so we can help better. I just don't have the time to do that. See the first link in my signature line for how to do that.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2015 at 6:03 pm
My apologies. H/T to Alan for the script to set up the DDL:
/****************************************************************************************
(1) Setup DDL
****************************************************************************************/
IF OBJECT_ID('dbo.Chooser') IS NOT NULL DROP TABLE dbo.Chooser;
IF OBJECT_ID('dbo.Book') IS NOT NULL DROP TABLE dbo.Book;
IF OBJECT_ID('dbo.Battle') IS NOT NULL DROP TABLE dbo.Battle;
CREATE TABLE dbo.Chooser
(
ChooserID int IDENTITY(1,1) NOT NULL,
ChooserName varchar(250) NOT NULL, -- NOT NULL is better
CONSTRAINT PK_Chooser PRIMARY KEY CLUSTERED (ChooserID)
);
CREATE TABLE dbo.Book
(
BookID int IDENTITY(1,1) NOT NULL,
BookTitle varchar(500) NOT NULL, -- NOT NULL is better
CONSTRAINT PK_Book PRIMARY KEY CLUSTERED (BookID)
);
CREATE TABLE dbo.Battle
(
BattleID int IDENTITY(1,1) NOT NULL,
WinnerBookID int NOT NULL,
LoserBookID int NOT NULL,
ChooserID int NOT NULL,
CONSTRAINT PK_Battle PRIMARY KEY CLUSTERED (BattleID)
);
GO
/****** Set up test data ******/
/****** Set Up Chooser Data ******/
SET IDENTITY_INSERT [dbo].[Chooser] ON
INSERT [dbo].[Chooser] ([ChooserID], [ChooserName]) VALUES (1, N'Bill')
INSERT [dbo].[Chooser] ([ChooserID], [ChooserName]) VALUES (2, N'Sue')
INSERT [dbo].[Chooser] ([ChooserID], [ChooserName]) VALUES (3, N'Larry')
SET IDENTITY_INSERT [dbo].[Chooser] OFF
/****** Set Up Book Data ******/
SET IDENTITY_INSERT [dbo].[Book] ON
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (1, N'Hadoop')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (2, N'NoSQL')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (3, N'T-SQL')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (4, N'SSIS')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (5, N'Oracle')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (6, N'PL/SQL')
SET IDENTITY_INSERT [dbo].[Book] OFF
/****** Set Up Battle Data ******/
SET IDENTITY_INSERT [dbo].[Battle] ON
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (1, 1, 2, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (2, 1, 3, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (3, 1, 4, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (4, 1, 5, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (5, 1, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (6, 2, 3, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (7, 2, 5, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (8, 2, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (9, 3, 4, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (10, 3, 5, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (11, 3, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (12, 4, 2, 1) -- Generates n=3 Paradox for Bill
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (13, 4, 5, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (14, 4, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (15, 5, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (16, 1, 2, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (17, 1, 3, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (18, 1, 4, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (19, 1, 5, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (20, 1, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (21, 2, 3, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (22, 2, 4, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (23, 2, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (24, 3, 4, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (25, 3, 5, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (26, 3, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (27, 4, 5, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (28, 4, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (29, 5, 2, 2) -- Generates n=3, n=4 Paradox for Sue
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (30, 5, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (31, 1, 2, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (32, 1, 3, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (33, 1, 4, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (34, 1, 5, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (35, 1, 6, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (36, 2, 3, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (37, 2, 4, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (38, 2, 5, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (39, 3, 4, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (40, 3, 5, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (41, 3, 6, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (42, 4, 5, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (43, 4, 6, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (44, 5, 6, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (45, 6, 2, 3) -- Generates n=3, n=4, n=5 Paradox for Larry
SET IDENTITY_INSERT [dbo].[Battle] OFF
Desired Results:
SELECT 'Bill' AS ChooserName, 'NoSQL, T-SQL, SSIS' AS CircularPreference
UNION SELECT 'Sue', 'NoSQL, SSIS, Oracle'
UNION SELECT 'Sue', 'NoSQL, T-SQL, Oracle'
UNION SELECT 'Sue', 'NoSQL, T-SQL, SSIS, Oracle'
UNION SELECT 'Larry', 'NoSQL, Oracle, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, SSIS, Oracle, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, SSIS, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, T-SQL, Oracle, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, T-SQL, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, T-SQL, SSIS, Oracle, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, T-SQL, SSIS, PL/SQL'
November 21, 2015 at 6:19 pm
jmpatchak (11/21/2015)
My apologies. H/T to Alan for the script to set up the DDL:
/****************************************************************************************
(1) Setup DDL
****************************************************************************************/
IF OBJECT_ID('dbo.Chooser') IS NOT NULL DROP TABLE dbo.Chooser;
IF OBJECT_ID('dbo.Book') IS NOT NULL DROP TABLE dbo.Book;
IF OBJECT_ID('dbo.Battle') IS NOT NULL DROP TABLE dbo.Battle;
CREATE TABLE dbo.Chooser
(
ChooserID int IDENTITY(1,1) NOT NULL,
ChooserName varchar(250) NOT NULL, -- NOT NULL is better
CONSTRAINT PK_Chooser PRIMARY KEY CLUSTERED (ChooserID)
);
CREATE TABLE dbo.Book
(
BookID int IDENTITY(1,1) NOT NULL,
BookTitle varchar(500) NOT NULL, -- NOT NULL is better
CONSTRAINT PK_Book PRIMARY KEY CLUSTERED (BookID)
);
CREATE TABLE dbo.Battle
(
BattleID int IDENTITY(1,1) NOT NULL,
WinnerBookID int NOT NULL,
LoserBookID int NOT NULL,
ChooserID int NOT NULL,
CONSTRAINT PK_Battle PRIMARY KEY CLUSTERED (BattleID)
);
GO
/****** Set up test data ******/
/****** Set Up Chooser Data ******/
SET IDENTITY_INSERT [dbo].[Chooser] ON
INSERT [dbo].[Chooser] ([ChooserID], [ChooserName]) VALUES (1, N'Bill')
INSERT [dbo].[Chooser] ([ChooserID], [ChooserName]) VALUES (2, N'Sue')
INSERT [dbo].[Chooser] ([ChooserID], [ChooserName]) VALUES (3, N'Larry')
SET IDENTITY_INSERT [dbo].[Chooser] OFF
/****** Set Up Book Data ******/
SET IDENTITY_INSERT [dbo].[Book] ON
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (1, N'Hadoop')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (2, N'NoSQL')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (3, N'T-SQL')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (4, N'SSIS')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (5, N'Oracle')
INSERT [dbo].[Book] ([BookID], [BookTitle]) VALUES (6, N'PL/SQL')
SET IDENTITY_INSERT [dbo].[Book] OFF
/****** Set Up Battle Data ******/
SET IDENTITY_INSERT [dbo].[Battle] ON
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (1, 1, 2, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (2, 1, 3, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (3, 1, 4, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (4, 1, 5, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (5, 1, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (6, 2, 3, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (7, 2, 5, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (8, 2, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (9, 3, 4, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (10, 3, 5, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (11, 3, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (12, 4, 2, 1) -- Generates n=3 Paradox for Bill
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (13, 4, 5, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (14, 4, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (15, 5, 6, 1)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (16, 1, 2, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (17, 1, 3, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (18, 1, 4, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (19, 1, 5, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (20, 1, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (21, 2, 3, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (22, 2, 4, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (23, 2, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (24, 3, 4, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (25, 3, 5, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (26, 3, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (27, 4, 5, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (28, 4, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (29, 5, 2, 2) -- Generates n=3, n=4 Paradox for Sue
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (30, 5, 6, 2)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (31, 1, 2, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (32, 1, 3, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (33, 1, 4, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (34, 1, 5, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (35, 1, 6, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (36, 2, 3, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (37, 2, 4, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (38, 2, 5, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (39, 3, 4, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (40, 3, 5, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (41, 3, 6, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (42, 4, 5, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (43, 4, 6, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (44, 5, 6, 3)
INSERT [dbo].[Battle] ([BattleID], [WinnerBookID], [LoserBookID], [ChooserID]) VALUES (45, 6, 2, 3) -- Generates n=3, n=4, n=5 Paradox for Larry
SET IDENTITY_INSERT [dbo].[Battle] OFF
Desired Results:
SELECT 'Bill' AS ChooserName, 'NoSQL, T-SQL, SSIS' AS CircularPreference
UNION SELECT 'Sue', 'NoSQL, SSIS, Oracle'
UNION SELECT 'Sue', 'NoSQL, T-SQL, Oracle'
UNION SELECT 'Sue', 'NoSQL, T-SQL, SSIS, Oracle'
UNION SELECT 'Larry', 'NoSQL, Oracle, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, SSIS, Oracle, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, SSIS, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, T-SQL, Oracle, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, T-SQL, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, T-SQL, SSIS, Oracle, PL/SQL'
UNION SELECT 'Larry', 'NoSQL, T-SQL, SSIS, PL/SQL'
Good work sir. Sorry that I disappeared, we got snowed on bad here in Chicago and it's been a rough/busy day. I'll try to put something together in the morning.
-- Itzik Ben-Gan 2001
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply