Android: How to use a join with SQLite

In one of my previous post I explained how to join two tables with SQLlite using a provider.
Android Using joins with a provider (SQLite)
That post may be a bit hard to understand if you do not have a provider. So in this post I will explain just how to join two tables in using SQLlite without all the provider stuff.

The tables

For this example I will use a SQLite database with two tables:

books
_id Id field
book_title Title of our book
category Id references to table categories.
categories
_id Id field
category_title Caption of our category

If you want you can create a helper class. This will be usefull when working with columns and table names.
These are my helper classes:

/**
 * Books table
 */
public static final class BookColumns implements BaseColumns {
	// This class cannot be instantiated
	private BookColumns () {}

	/**
	 * The table name of books = "books"
	 */
	public static final String TABLENAME = "books";
	
	/**
	 * The id of the category, includes tablename prefix
	 * <P>Type: INT</P>
	 */
	public static final String FULL_ID =  TABLENAME + "." + _ID;	

	/**
	 * The title of the book
	 * <P>Type: TEXT</P>
	 */
	public static final String BOOK_TITLE = "book_title";        

	/**
	 * The category of the book (ref to table categories)
	 * <P>Type: INT</P>
	 */
	public static final String CATEGORY = "category";        
}

/**
 * Categories table
 */
public static final class CategoriesColumns implements BaseColumns {
	// This class cannot be instantiated
	private CategoriesColumns () {}

	/**
	 * The table name of categories = "categories"
	 */
	public static final String TABLENAME = "categories";
	
	/**
	 * The id of the category, includes tablename prefix
	 * <P>Type: INT</P>
	 */
	public static final String FULL_ID =  TABLENAME + "." + _ID;	

	/**
	 * The caption of the category
	 * <P>Type: TEXT</P>
	 */
	public static final String CATEGORY_TITLE = "category_title";        
}

Note that I included the fields FULL_ID, this is because the fieldname is the same for both tables, in our query we will need to specify which _id field needs to be used.

Joining the tables

Now to create a query:

//Create new querybuilder
SQLiteQueryBuilder _QB = new SQLiteQueryBuilder();

//Specify books table and add join to categories table (use full_id for joining categories table)
_QB.setTables(BookColumns.TABLENAME + 
		" LEFT OUTER JOIN " + CategoryColumns.TABLENAME + " ON " + 
		BookColumns.CATEGORY + " = " + CategoryColumns.FULL_ID);
		
//Order by records by title
_OrderBy = BookColumns.BOOK_TITLE + " ASC";

//Open database connection
SQLiteDatabase _DB = fDatabaseHelper.getReadableDatabase();

//Get cursor
Cursor _Result = _QB.query(_DB, null, null, null, null, null, _OrderBy);

Projection map

If you need you can also add a projection map to specify the columns, see my previous post for an example:
Android Using joins with a provider (SQLite)

That’s it for this post, till next time :)

avatar

About Martin

Martin van Zuilekom has 12 years experience working as a professional software developer. Main focus points are user interface design and framework architecture. Follow Martin on Google plus

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>