title: Android SQLite Querying for Foreign Key
body: <p>I have two models</p>
  
  <pre><code>categories(id, name, slug)
  places(id, name, cat_id)
  </code></pre>
  
  <p>when i have to insert the place details into the database i have a category <code>slug</code> string. So i will need to query the categories id and insert it in to the places <code>category_id</code></p>
  
  <p>DBAdapter.java</p>
  
  <pre><code>public class DBAdapter {
      // Database and version
      private static final String DATABASE_NAME = "nomad.db";
      private static final int DATABASE_VERSION = 2;
  
      // Tables
      public static final String TABLE_CATEGORIES = "categories";
      public static final String TABLE_PLACES = "places";
      public static final String TABLE_PHOTOS = "photos";
  
      // Table Columns
      public static final String COLUMN_ID = "_id";
      public static final String COLUMN_NAME = "name";
      public static final String COLUMN_SLUG = "slug";
      public static final String COLUMN_ACTIVE = "active";
  
      public static final String COLUMN_DISTANCE = "distance";
      public static final String COLUMN_DAYS = "days";
      public static final String COLUMN_LAT = "latitude";
      public static final String COLUMN_LNG = "longitude";
      public static final String COLUMN_WEATHER = "weather";
      public static final String COLUMN_TODO = "todo";
      public static final String COLUMN_ABOUT = "about";
  
      public static final String COLUMN_URL = "url";
      public static final String COLUMN_COPY = "copyright";
  
      public static final String COLUMN_CAT_ID = "category_id";
      public static final String COLUMN_PLACE_ID = "place_id";
  
      // Table Queries
      private static final String CREATE_CATEGORIES = "CREATE TABLE categories (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, slug TEXT, active BOOLEAN);";
      private static final String CREATE_PLACES = "CREATE TABLE places (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, slug TEXT, category_id INTEGER, distance TEXT, days TEXT, latitude TEXT, longitude TEXT, weather TEXT, todo TEXT, about TEXT);";
      private static final String CREATE_PHOTOS = "CREATE TABLE photos (_id INTEGER PRIMARY KEY AUTOINCREMENT, place_id INTEGER, url TEXT, copyright TEXT);";
  
      private final Context context;
      private static DatabaseHelper DBHelper;
      private SQLiteDatabase db;
  
      public DBAdapter(Context ctx) {
          this.context = ctx;
          DBHelper = new DatabaseHelper(context);
      }
  
      private static class DatabaseHelper extends SQLiteOpenHelper {
  
          public DatabaseHelper(Context context) {
              super(context, DATABASE_NAME, null, DATABASE_VERSION);
              // TODO Auto-generated constructor stub
          }
  
          @Override
          public void onCreate(SQLiteDatabase database) {
              database.execSQL(CREATE_CATEGORIES);
              database.execSQL(CREATE_PLACES);
              database.execSQL(CREATE_PHOTOS);
          }
  
          @Override
          public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
              Log.w("Nomad", "Upgrading database from version " + oldVersion
                      + " to " + newVersion + ", which will destroy all old data");
              db.execSQL("DROP TABLE IF EXISTS categories");
              db.execSQL("DROP TABLE IF EXISTS places");
              db.execSQL("DROP TABLE IF EXISTS photos");
              onCreate(db);
          }
  
      }
  
      public DBAdapter open() throws SQLException {
          db = DBHelper.getWritableDatabase();
          return this;
      }
  
      public void close() {
          DBHelper.close();
      }
  
      // Methods
  
      // Categories
  
      // Insert Category
      public long insertCategory(String name, String slug) {
          ContentValues initialValues = new ContentValues();
          initialValues.put(COLUMN_NAME, name);
          initialValues.put(COLUMN_SLUG, name);
          initialValues.put(COLUMN_ACTIVE, true);
          return db.insert(TABLE_CATEGORIES, null, initialValues);
      }
  
      // Get Category ID by Slug
      public int find_city_id(String slug) {
          Cursor c = db.query(TABLE_CATEGORIES, new String[] { COLUMN_ID }, COLUMN_SLUG + "=" + "'" + slug + "'", null, null, null, null, null);
          if (c.moveToFirst()) {
              do{
                  return Integer.parseInt(c.getString(0));
              } while (c.moveToNext());
          }
          else
              return -1;
      }
  
  
      // Places
      public long insertPlace(String name, String slug, String distance,
              String days, String latitude, String longitude, String weather,
              String todo, String about, int category_id) {
          ContentValues initialValues = new ContentValues();
          initialValues.put(COLUMN_NAME, name);
          initialValues.put(COLUMN_SLUG, slug);
          initialValues.put(COLUMN_DISTANCE, distance);
          initialValues.put(COLUMN_DAYS, days);
          initialValues.put(COLUMN_LAT, latitude);
          initialValues.put(COLUMN_LNG, longitude);
          initialValues.put(COLUMN_WEATHER, weather);
          initialValues.put(COLUMN_TODO, todo);
          initialValues.put(COLUMN_ABOUT, about);
          return db.insert(TABLE_PLACES, null, initialValues);
      }
  
  }
  </code></pre>