On 6 May 2010, at 10:35am, Ian Hardingham wrote: > CREATE TABLE IF NOT EXISTS globalRankingTable (id INTEGER PRIMARY KEY > AUTOINCREMENT, name TEXT NOT NULL UNIQUE, ranking TEXT, score REAL, > record TEXT); > > And a "friends" table which looks like this: > > CREATE TABLE IF NOT EXISTS friendTable (id INTEGER PRIMARY KEY > AUTOINCREMENT, simpleId TEXT NOT NULL UNIQUE, player TEXT, friend TEXT);
You have done pretty well so far, but there are some improvements. First, you should be storing INTEGER ids in your friendTable, not TEXT names: > CREATE TABLE IF NOT EXISTS friendTable ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > simpleId TEXT NOT NULL UNIQUE, > playerID INTEGER, > friendID INTEGER); It's faster to match INTEGERs than it is TEXT fields. Also this lets someone change their name (they spelled it wrong or got married) without losing their ranking. I think you can do without your 'simpleID' field. Your one-to-one correspondance between name and ID is in globalRankingTable. In your program, shortly after the user enters their name, look up their ID in globalRankingTable. You must already do this, because you'd need to do it to work out if you need to create a new entry there. From then on, use IDs rather than names. > I need to: > > Select all from globalRankingTable > > Go through each row > > Find whether "name" is a "friend" of my current user (ie, does name > concatinated with username appear as "simpleid" in friendTable) Okay, so you don't really need to SELECT ALL, you just want to SELECT the rows where playerID indicates your current player: SELECT * FROM friendTable WHERE playerID = [[whatever variable you keep currentPlayerID in]] This will go far faster if you CREATE an INDEX on friendTable for the column playerID. but you probably want to retrieve the names and scores of the friends, so you can use a JOIN to retrieve them: SELECT name,ranking,score FROM globalRankingTable JOIN friendTable ON friendTable.friendID = globalRankingTable.id WHERE globalRankingTable.playerID = [[whatever variable you keep currentPlayerID in]] and to list them in descending order (the friend who is doing best at the top) add this on the end ORDER BY score DESC Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
