In addition to solution #1 by
Wendelius[
^], i'd change your database design as follow (tables):
1. User (stores information about users)
2. Game (stores information about games, like: poker, bridge, etc.)
3. Tour (stores information about games started at specific time /such of session/)
4. Tour_Details (stores information about users playing game and their results)
DECLARE @user TABLE(UserId INT IDENTITY(1,1), UserName VARCHAR(50))
INSERT INTO @user(UserName)
VALUES('John'), ('Gina'), ('Frank'), ('Dolores'), ('Sebastian'), ('Angel'), ('Robert'), ('Eve')
DECLARE @game TABLE(GameId INT IDENTITY(1,1), GameName VARCHAR(50))
INSERT INTO @game(GameName)
VALUES('Poker'), ('Bridge')
DECLARE @tour TABLE(TourId INT IDENTITY(1,1), StartedAt DATETIME, GameId INT)
INSERT INTO @tour(StartedAt, GameId )
VALUES('2018-04-25 18:05:37.000', 1), ('2018-04-25 21:15:12.000', 2)
DECLARE @tour_details TABLE(TourDetailId INT IDENTITY(1,1), TourId INT, UserId INT, ReachedPoints INT)
INSERT INTO @tour_details(TourId, UserId, ReachedPoints)
VALUES(1, 1, 0), (1, 2, 2500), (1, 3, 750), (1, 4, 500),
(2, 7, 2500), (2, 8, -500), (2, 5, 2500), (2, 6, -500)
SELECT TR.TourId, G.GameName, T.StartedAt, U.UserName, TR.ReachedPoints
FROM @tour_details AS TR
INNER JOIN @user AS U ON TR.UserId = U.UserId
INNER JOIN @tour AS T ON TR.TourId = T.TourId
INNER JOIN @game AS G ON G.GameId = T.GameId
ORDER BY TR.TourId ASC, TR.ReachedPoints DESC
Sample output:
TourId GameName StartedAt UserName ReachedPoints
1 Poker 2018-04-25 18:05:37.000 Gina 2500
1 Poker 2018-04-25 18:05:37.000 Frank 750
1 Poker 2018-04-25 18:05:37.000 Dolores 500
1 Poker 2018-04-25 18:05:37.000 John 0
2 Bridge 2018-04-25 21:15:12.000 Sebastian 2500
2 Bridge 2018-04-25 21:15:12.000 Robert 2500
2 Bridge 2018-04-25 21:15:12.000 Eve -500
2 Bridge 2018-04-25 21:15:12.000 Angel -500
As you can see, whenever users start playing new game, new tour is opened, then
TourID
within every single
UserID
is stored in
Tour_Details
table. Such of design enable you to store details about single- and multi-users games.
Good luck!