Titanium SDK: Selecting Data from Multiple Tables in SQLite with JOIN

Assumptions

For this example, I’m assuming we get our user_ID populated from a web service when the app starts.

Create your tables

There’re 3 tables, one for game text info, one for game icon graphics and one that stores a list of my favorite games.

 
CREATE TABLE MyGames (
      game_ID INTEGER PRIMARY KEY,
      game_name TEXT,
      num_players INTEGER,
      game_rules TEXT
);   

CREATE TABLE MyGameIcons (
      icon_ID INTEGER PRIMARY KEY,
      game_ID INTEGER,
      icon_ImgPth TEXT,
      detail_ImgPth TEXT
); 

CREATE TABLE MyFaveGames (
	   fave_ID INTEGER PRIMARY KEY,
	   game_ID INTEGER, 
	   user_ID INTEGER 
	)

Add some dummy data to them using SQLite Manager plugin for Firefox or a command line tool. Once you have some data, you can move on to trying out the JOIN clause.

Use a basic INSERT to save a game to the MyFaveGames table:

 
INSERT INTO MyFaveGames(user_ID, game_ID) VALUES(1823, 9)

To use this statement in Titanium SDK 2.x app, you can do this:

 
db.execute( 'INSERT INTO MyFaveGames(user_ID, game_ID) VALUES(?, ?)', varUserID, varGameID );

Assuming varUserID and varGameID are pre-populated in your app and db is defined earlier as your database connection object ( db = Ti.Database.open(myConn); )

Use SQL’s implicit JOIN clause to pull records from more than one table using one query

Pull the game info & icon graphics for games saved to My Favorite Games. Order them by the game names.

 
SELECT MyGames.game_ID, game_name, num_players, game_rules, icon_ImgPth, detail_ImgPth FROM MyGames, MyGameIcons, MyFaveGames WHERE MyFaveGames.game_ID=MyGames.game_ID AND MyFaveGames.game_ID=MyGameIcons.game_ID ORDER BY game_name

In Titanium SDK 2.x app, it can look like this:

 
db.execute('SELECT MyGames.game_ID, game_name, num_players, game_rules, icon_ImgPth, detail_ImgPth FROM MyGames, MyGameIcons, MyFaveGames WHERE MyFaveGames.game_ID=MyGames.game_ID AND MyFaveGames.game_ID=MyGameIcons.game_ID ORDER BY game_name');

The above would return a recordset, which you can loop through.

Creating a Temporary Table for Immediate Use

 
		var myDBconn = getDB();

		var temp = myDBconn.execute('CREATE TEMP VIEW MyTempTabl AS SELECT fave_ID, game_ID, user_ID FROM MyFaveGames WHERE user_ID=' + varUserID);
		var rows = myDBconn.execute('SELECT MyGames.game_ID, game_name, num_players, equip_items, fld_type, classics, fancies, game_instructns, fld_Icon, equip_Icon, composit_Icon, fave_ID, user_ID FROM MyGames LEFT OUTER JOIN MyGameIcons on MyGames.game_ID = MyGameIcons.game_ID LEFT OUTER JOIN MyTempTabl ON MyGames.game_ID = MyTempTabl.game_ID WHERE classics = "TRUE" ORDER BY game_name'); 
Advertisements

One thought on “Titanium SDK: Selecting Data from Multiple Tables in SQLite with JOIN

  1. Tim, I would like to get in touch with you regarding a unity3d+AR/CV project Im working on. I was looking for your email address at timshaya[dot]com but didn’t find a contact link there. Please get in touch with me when you have a moment,.. via my email at [edited out…]

    …..and thanks for the Ar+Unity Dev tutorials a few months back. Much appreciated.

    -Zach

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s