SQL help

(Using Sqlite, gmod’s sv.db)
I have two SQL tables. One containing SteamIDs, one containing IP Addresses. The primary key of the SteamID table is a foreign key in the IP address table, while the primary key in the IP Address table is effectively unused.

Supposing I had data that looked like this.
http://imghub.co/v/qd8wg

I need a query that, when passed SteamID1, returns SteamID2, 3, and 4. This is a little beyond my SQL skills, and when I attempted to use recursive Lua with simpler queries, I got unreliable results.

I’m not really understanding what you’re trying to do here.

Are SteamID1, SteamID2, 3, 4, 5 as well as IP1, IP2, 3, 4, 5 fields?

I understand you would need a query, though I’m not exactly sure what data you want the queries to return.

Could you properly show us the data structures? By this I mean screenshots of the tables or producing an entity relationship diagram.

SteamID1 … 5 are records from the ID table, IP1…5 are records from the IP table. The red lines indicate a connection via foreign key.

Quick and dirty tables for easy understanding:
CREATE TABLE IDTable (
id INTEGER NOT NULL PRIMARY KEY,
steamid TEXT NOT NULL
)
CREATE TABLE IPTable (
id INTEGER NOT NULL PRIMARY KEY,
uid INTEGER NOT NULL,
ip TEXT NOT NULL
)

IPTable.uid == IDTable.id

What I want back, is a list of all steamIDs that have a connection to a passed steamid. So if another steamID connected from the same IP address, it would find that. Now take all IP addresses of the new steamID, and add any other steamIDs that have connected from those. The end result would effectively be finding a list of any and (hopefully) all alt accounts belonging to a particular user.

Is there any particular reason to why you want have separate tables for STEAMIDs’ and IP addresses’ ?

Normalisation is good a practice, but we don’t really need break down fields further. You are only dealing with two fields (which are ‘ip’ and ‘steamid’), which shouldn’t need to broken down further.

As well it seems to me that you’re creating a reporting script, which shouldn’t really need to be normalised so let’s just stick with one table. It’s much easier to just have one table, as there would be one location, less extra fields and faster queries.

So let’s use this one table ‘Players’:



CREATE TABLE Players (
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
steamid TEXT NOT NULL,
ip TEXT NOT NULL
);


Now let’s place some example data (‘test’ is a database name):



INSERT INTO `test`.`players` (`id`, `steamid`, `ip`) VALUES (NULL, 'STEAM:0:00:00', '127.0.0.1');

INSERT INTO `test`.`players` (`id`, `steamid`, `ip`) VALUES (NULL, 'STEAM:0:00:01', '827.96.69.79'); -- !

INSERT INTO `test`.`players` (`id`, `steamid`, `ip`) VALUES (NULL, 'STEAM:0:00:03', '827.96.69.79'); -- !

INSERT INTO `test`.`players` (`id`, `steamid`, `ip`) VALUES (NULL, 'STEAM:0:00:04', '192.168.0.1');

INSERT INTO `test`.`players` (`id`, `steamid`, `ip`) VALUES (NULL, 'STEAM:0:00:05', '255.255.255.0');


Result:



id	steamid	        ip
1	STEAM:0:00:00	127.0.0.1
2	STEAM:0:00:01	827.96.69.79
3	STEAM:0:00:03	827.96.69.79
4	STEAM:0:00:04	192.168.0.1
5	STEAM:0:00:05	255.255.255.0


Now as being stated, a query is needed to find players who the same IP address.



SELECT * FROM players WHERE ip IN (
SELECT ip FROM players 
GROUP BY ip HAVING COUNT(*) > 1
)


Result:



id	steamid	        ip
2	STEAM:0:00:01	827.96.69.79
3	STEAM:0:00:03	827.96.69.79


EDIT:

Made a link for SQLFiddle for SQLite

Click here!

Except SteamID<–>IP is One to Many, not One to One. I wouldn’t be asking for help it if was One to One.

I’m writing this off as a lost cause. I’ve already got a workaround that’s Lua-based, and I can get away with it because it’s a local database.

The relationships make sense, my bad. I should have reliased that.

Anyways, what is the LUA based solution you have?

I managed to make the query for both tables



-- Starting with tables


CREATE TABLE IDTable (
id INTEGER NOT NULL PRIMARY KEY,
steamid TEXT NOT NULL
);

CREATE TABLE IPTable (
uid INTEGER NOT NULL,
ip TEXT NOT NULL,
FOREIGN KEY(uid) REFERENCES IDTable(id)
);
--------------------------------------------------

-- Now to place example data

-- Player 1
INSERT INTO IDTable 
VALUES(NULL, "STEAM:0:00:01");

INSERT INTO IPTable 
VALUES(1, "192.168.0.1");

-- Player 2
INSERT INTO IDTable 
VALUES(NULL, "STEAM:0:00:02");

INSERT INTO IPTable 
VALUES(2, "827.96.69.79");

-- Player 3
INSERT INTO IDTable 
VALUES(NULL, "STEAM:0:00:03");

INSERT INTO IPTable 
VALUES(3, "827.96.69.79");

-- Player 4 
INSERT INTO IDTable 
VALUES(NULL, "STEAM:0:00:04");

INSERT INTO IPTable 
VALUES(4, "255.255.255.0");

-- Player 5
INSERT INTO IDTable 
VALUES(NULL, "STEAM:0:00:05");

INSERT INTO IPTable 
VALUES(5, "827.96.69.79");

-- Player 6
INSERT INTO IDTable 
VALUES(NULL, "STEAM:0:00:06");

INSERT INTO IPTable 
VALUES(6, "127.0.0.1");

-- Player 7
INSERT INTO IDTable 
VALUES(NULL, "STEAM:0:00:07");

INSERT INTO IPTable 
VALUES(7, "127.0.0.1");


Result:



-- IDTable:

id	steamid
1	STEAM:0:00:01
2	STEAM:0:00:02
3	STEAM:0:00:03
4	STEAM:0:00:04
5	STEAM:0:00:05
6	STEAM:0:00:06
7	STEAM:0:00:07

----------------------------------

-- IPTable:

uid	ip
1	192.168.0.1
2	827.96.69.79
3	827.96.69.79
4	255.255.255.0
5	827.96.69.79
6	127.0.0.1
7	127.0.0.1


Now for the query:



SELECT IDTABLE.id, IDTable.steamid, ip 
FROM IPTable
JOIN IDTable
ON id = uid
WHERE ip IN(
SELECT ip FROM IPTable
GROUP BY ip HAVING COUNT(*) > 1
)


Result:



id	steamid	        ip
2	STEAM:0:00:02	827.96.69.79
3	STEAM:0:00:03	827.96.69.79
5	STEAM:0:00:05	827.96.69.79
6	STEAM:0:00:06	127.0.0.1
7	STEAM:0:00:07	127.0.0.1


Click here for the SQL Fiddle demo

Two issues with your search query: One, it’s an unfiltered list, which could theoretically contain hundreds of IDs, when you’re only interested in half a dozen in the more extreme cases.

Two, in my attempts to filter such a list, it is reduced to only direct connections. I’m trying to have the query make the indirect connections as well. That’s what my workaround does right now:


local res = {}
local seen = {[sid] = true}
local queue = {sid}
while #queue > 0 do
	local id = table.remove(queue, 1)
	local tab = sql.Query(Queries[GETRELATED]:format(id))
	if !tab then continue end
	for k,v in pairs(tab) do
		if !seen[v.steamid] then
			seen[v.steamid] = true
			table.insert(queue, v.steamid)
			table.insert(res, v.steamid)
		end
	end
end
return res

Where ‘GETRELATED’ = a query close to yours, but replace GROUP BY etc. with WHERE uid = (SELECT id FROM IDTable where steamid = ‘STEAM_0:0:0’). And maybe one or two other alterations, but the same objective.

But again, I can only get away with that because it’s a local database. Ideally, I’d like to be able to add support for remote databases in the future, but that means everything needs to be done in as few queries as possible, for sanity’s sake.

Edit: Sorry for not being very forthcoming with the actual code and queries. It’s something I’d rather not post publicly at present. Maaaybe after I’m finished. We’ll see.