Creating a Ping Pong data schema for MS SQL

CreatePingPongSchema

-- Create schema
CREATE TABLE Player
(
    Id int IDENTITY(1,1),
    FirstName nvarchar(max) NOT NULL,
    LastName nvarchar(max) NULL,

	PRIMARY KEY([Id])
)

CREATE TABLE Match
(
    Id int IDENTITY(1,1),
    Name nvarchar(max) NOT NULL,
    WinnerPlayerId int NOT NULL,
    LooserPlayerId int NOT NULL,

	PRIMARY KEY([Id])
)

ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT FK_Match_WinnerPlayerId FOREIGN KEY([WinnerPlayerId]) REFERENCES [dbo].[Player] ([Id]);
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT FK_Match_LooserPlayerId FOREIGN KEY([LooserPlayerId]) REFERENCES [dbo].[Player] ([Id]);

-- Enter test data
INSERT INTO [dbo].[Player] ([FirstName],[LastName]) VALUES ('Peter', 'Stegnar');
INSERT INTO [dbo].[Player] ([FirstName],[LastName]) VALUES ('J. Shane', 'Kunkle');
INSERT INTO [dbo].[Player] ([FirstName],[LastName]) VALUES ('Joe', 'Doe');

INSERT INTO [dbo].[Match] ([Name],[WinnerPlayerId],[LooserPlayerId]) VALUES('USA Open 1',1,2);
INSERT INTO [dbo].[Match] ([Name],[WinnerPlayerId],[LooserPlayerId]) VALUES('USA Open 2',1,2);
INSERT INTO [dbo].[Match] ([Name],[WinnerPlayerId],[LooserPlayerId]) VALUES('USA Open 3',2,1);
INSERT INTO [dbo].[Match] ([Name],[WinnerPlayerId],[LooserPlayerId]) VALUES('USA Open 4',2,1);
INSERT INTO [dbo].[Match] ([Name],[WinnerPlayerId],[LooserPlayerId]) VALUES('USA Open 5',2,2);
INSERT INTO [dbo].[Match] ([Name],[WinnerPlayerId],[LooserPlayerId]) VALUES('USA Open 5',1,3);

-- Query

tries


SELECT p.FirstName as Name, COUNT(m1.LooserPlayerId) as Losees--, COUNT(m2.LooserPlayerId) Wins

FROM Player p JOIN Match m1 ON p.Id = m1.LooserPlayerId --FULL JOIN Match m2 ON p.Id = m2.WinnerPlayerId 

GROUP BY p.FirstName, m1.LooserPlayerId--, m2.WinnerPlayerId;



SELECT p.FirstName as Name, COUNT(m2.LooserPlayerId) Wins

FROM Player p JOIN Match m2 ON p.Id = m2.WinnerPlayerId 

GROUP BY p.FirstName, m2.WinnerPlayerId;





SELECT p.FirstName, COUNT(m.WinnerPlayerId), COUNT(m.LooserPlayerId)

FROM Match m JOIN Player p ON m.LooserPlayerId = p.Id

GROUP BY p.FirstName, m.WinnerPlayerId, m.LooserPlayerId;










SELECT p.FirstName as Name, COUNT(m.LooserPlayerId) as Losees, COUNT(m.LooserPlayerId) Wins

FROM Player p, Match m

WHERE p.Id = m.LooserPlayerId OR p.id = m.WinnerPlayerId

GROUP BY p.FirstName, m.LooserPlayerId, m.WinnerPlayerId;




SELECT COUNT(m.LooserPlayerId), COUNT(m.WinnerPlayerId)

FROM Player p, Match m

WHERE p.Id = m.LooserPlayerId OR p.id = m.WinnerPlayerId

GROUP BY m.LooserPlayerId, m.WinnerPlayerId;