*solved* SQLite and a gaming server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

*solved* SQLite and a gaming server

Author

Topic

MALON
Starting Member

5 Posts

Posted - 2008-08-18 : 15:49:03

I've never posted any questions about SQLite before, so I don't know what information I need to give you about it, so I'll just link you to a copy of my SQL database: *removed*

I have taken over maintaining a plugin for a game because the original author stopped working on it, and I'd like to add a query to part of the source code of the game. This is basically a racing game.

Here's the breakdown of what I'm doing:

Player says "/mytoptimes" and it returns all maps in which they have hold the #1 fastest record for and the time associated with it. Example: If there are 5 tracks: A, B, C, D, and E, and they hold the record for B and D, the query should return B and D, along with the associated times.

Here are the important tables/columns that go with this query (if you can't use the DB I provided)

Table 1:
climb_scores

columns for climb_scores
user_id (int)(unique identifier)
map_name (string)(track name)
fin_time (float)(players finishing time

Table 2:
climb_players

columns for climb_players
user_id (int)(same as previous table, useful for joins)
alias (string)(players recorded name)
steam_id (string)(unique identifier, explained below)

Every Valve/steam account has a unique number attached to it, the Steam ID. When the player says "/mytoptimes", I will pass that players Steam ID to the query in the form of a variable so you can get the top times of the specific player based on Steam ID. For an example Steam ID, you can use mine:

STEAM_0:1:11718381

Everything in the database is non-sensitive information, so don't worry about that. Steam ID's are public information.

I hope I've provided enough information.

Example Format of Result:

MALON bkz_goldbhop 2:42
MALON cg_lighthops 3:59
MALON kz_world 22:08
MALON kz_whatever 0:00
etc.

--MALON

Solved with this query:

select map_name, fin_time
from climb_players as player
join (
select user_id, map_name, fin_time
from climb_scores as score
join (
select map_name as name, min(fin_time) as time
from climb_scores group by map_name
) as fastest
where score.map_name = fastest.name
and score.fin_time = fastest.time
) as records
where player.steam_id = *insert Steam ID here*
and player.user_id = records.user_id;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-18 : 17:52:58

MALON,

I realize you've solved your problem, however I wanted to bring this to your attention:

This is a Microsoft SQL Server site. Any answers that we provide will be for SQL Server and may not work in SQLite. Looking at your solution, the syntax is similar.

I'm not sure if there is a forum for SQLite or not, but perhaps you could google for one.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

MALON
Starting Member

5 Posts

Posted - 2008-08-19 : 02:45:00

Sorry for the post and I was unaware that this was only for Microsoft SQL Server only. I apologize. I searched "SQLite" and came back with results, so I quickly made the wrong assumption that it was discussed here.

Thank you, and sorry for the post. You may remove it.

--MALON

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources