SQL Server : inserting Player vs Player names in to new table from tblEntrants

Question

I have a table tblEntrants which is a list of pool players. I want to draw fixtures for them so I need to take the top half of the list and play them against the bottom half of the list by copying them into tblFixtures.

tblFixtures has 3 columns: player1, player2 and compID.

This is what I have so far:

INSERT INTO tblFixtures (player1, compID)
    SELECT TOP (50) PERCENT 
        accountID, compID 
    FROM 
        tblEntrants
    WHERE 
        paid = 'y'  AND compID = @compID 
    ORDER BY 
        accountID ASC

INSERT INTO tblFixtures (player2)
    SELECT TOP (50) PERCENT accountID 
    FROM tblEntrants
    WHERE paid = 'y' AND compID = @compID 
    ORDER BY accountID DESC

But this does this...

Player1   Player 2   CompID
---------------------------
Bob     v null       {Guid}
Bill    v null       {Guid}
Ben     v null       {Guid}
null    v Matt       {Guid}
null    v Mick       {Guid}
null    v Mark       {Guid}

I need to adjust the above code to add the bottom half of the players in to the Player2 column where the first half of the players exist. Like this..

Player1   Player 2   CompID
---------------------------
Bob     v Matt       {Guid}
Bill    v Mick       {Guid}
Ben     v Mark       {Guid}

Any help appreciated

Answer 1

First insert rows randomly

INSERT INTO tblFixtures (player1, compID)
SELECT TOP (50) PERCENT accountID, compID FROM tblEntrants
WHERE paid = 'y'  
AND compID=@compID 
ORDER BY NEWID()

Without a primary key in the tblFixtures table, this is all I can think of to update the table. It's a loop (there's almost always a way to avoid a loop in SQL!!). Also you could probably write some mega SQL statement that does it in one go... but hopefully this makes sense.

DECLARE @player1 uniqueidentifier , @player2 uniqueidentifier ;

SELECT 1;

WHILE @@ROWCOUNT <> 0
BEGIN

    SELECT @player1= NULL, @player2=NULL;

    PRINT 'Finding unmatched player';

    -- Find a player without an opponent
    SELECT TOP 1 @player1 = player1
    FROM tblFixtures
    WHERE compID=@compID 
    AND player2 IS NULL;

    PRINT 'Found unmatched player ' + CAST(@player1 AS VARCHAR(100));

    -- Find a player not in a fixture
    SELECT TOP 1 @player2 = accountID 
    FROM tblEntrants
    WHERE paid = 'y'  
    AND compID=@compID 
    AND accountID  NOT IN (
         SELECT player1 
         FROM tblFixtures 
         WHERE compID=@compID
         AND player1 IS NOT NULL
         )
    AND accountID  NOT IN (
         SELECT player2 
         FROM tblFixtures 
         WHERE compID=@compID
         AND player2 IS NOT NULL
         )


    PRINT 'Found unassigned player ' + CAST(@player2 AS VARCHAR(100));

    -- Assign the opponent
    UPDATE tblFixtures
    SET player2 = @player2
    WHERE player1 = @player1
    AND compID = @compID;

END

Answer 2

Consider a cross join of top and bottom half players, so every player is paired with every possible match with another player and add a WHERE clause in case of an uneven list and median cut-off is the same player. And in SQL Server ORDER BY is allowed in a derived table with TOP specified.

INSERT into tblFixtures (player1, player2, compID)
SELECT p1.accountID, p2.accountID, p1.compID
FROM
    (SELECT TOP (50) PERCENT accountID, compID FROM tblEntrants
     WHERE paid = 'y' AND compID=@compID ORDER BY accountID ASC) p1
CROSS JOIN 
    (SELECT TOP (50) PERCENT accountID FROM tblEntrants
     WHERE paid = 'y' AND compID=@compID ORDER BY accountID DESC) p2
WHERE p1.accountID <> p2.accountID

Do note: CROSS JOIN can become expensive very quickly as it returns a cartesian product (M x N) where total rows is the product of both sets.

Answer 3

INSERT into tblFixtures (player1, compID, player2)
SELECt top1.accountID,compID, least1.accountID
  FROM
    (SELECT accountID, compID , 
            ROW_NUMBER() OVER (ORDER BY accountID) rn
       FROM tblEntrants
      WHERE paid = 'y'  AND compID=@compID) top1,

    (SELECT accountID ,
            ROW_NUMBER() OVER (ORDER BY accountID desc) rn
       FROM tblEntrants
    WHERE paid = 'y'  AND compID=@compID ) least1
WHERE top1.rn = least1.rn
  AND top1.rn <= (select round(count(*)/2,0) from tblEntrants where paid = 'y' and compID=@compID )