SQL statements for querying game databases in OCGDB (SQLite)

[pham]

1) Query full games
OCGDB databases divide data into 4 main tables, linked by ID:
- Players: player info such as names, Elo
- Events: event info such as names, Elo
- Sites: site info such as names, Elo
- Games: all games info such as results, lengths, FENs, Moves

To query full games we need to join the above tables:

Code: Select all

SELECT g.ID GID, e.Name Event, s.Name Site, w.Name White, b.Name Black, g.*
FROM Games g
INNER JOIN Events e ON EventID = e.ID
INNER JOIN Sites s ON SiteID = s.ID
INNER JOIN Players w ON WhiteID = w.ID
INNER JOIN Players b ON BlackID = b.ID

2. Query games of a player

Similar to query full games (1), we add a condition about the player:

Code: Select all

WHERE White = 'Carlsen, Magnus' OR Black = 'Carlsen, Magnus'

The statement becomes:

Code: Select all

SELECT g.ID GID, e.Name Event, s.Name Site, w.Name White, b.Name Black, g.*
FROM Games g
INNER JOIN Events e ON EventID = e.ID
INNER JOIN Sites s ON SiteID = s.ID
INNER JOIN Players w ON WhiteID = w.ID
INNER JOIN Players b ON BlackID = b.ID
WHERE White = 'Carlsen, Magnus' OR Black = 'Carlsen, Magnus'

3. Query games of a player with LIKE

Sometimes the data is not consistent, a player name can be stored in different strings such as 'Carlsen, Magnus', 'Carlsen, M.', 'Magnus, Carlsen'. Or sometimes we want to find out all players with the family name 'Smith'. We can use statement LIKE:

Code: Select all

SELECT g.ID GID, e.Name Event, s.Name Site, w.Name White, b.Name Black, g.*
FROM Games g
INNER JOIN Events e ON EventID = e.ID
INNER JOIN Sites s ON SiteID = s.ID
INNER JOIN Players w ON WhiteID = w.ID
INNER JOIN Players b ON BlackID = b.ID
WHERE White LIKE '%Carlsen%'