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:

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 :)