Android - How to retrieve data from multiple SQLite tables?

Question

I have two tables and I want to show parts of both in a list view using a list Activity. But only one of the tables is showing. I have a foreign key set but nothing except the one table is showing.

My Tables

db.execSQL("CREATE TABLE " + WW_TABLE + 
                " (" + KEY_ROWIDLL + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_LAT + " TEXT NOT NULL, " + 
                KEY_LON + " TEXT NOT NULL);");
        db.execSQL("CREATE TABLE " + WW_TIMETABLE + 
                " (" + KEY_ROWIDTIME + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                KEY_HOUR + " TEXT NOT NULL, " +
                KEY_FOREIGN + " INTEGER," +
                " FOREIGN KEY ("+KEY_FOREIGN+") REFERENCES "+WW_TABLE+" ("+KEY_ROWIDLL+") ON DELETE CASCADE);");

How I query the information

public Cursor fetchTime() {
    // TODO Auto-generated method stub
    return ourdb.query(WW_TIMETABLE, new String[] {KEY_ROWIDTIME, KEY_HOUR, KEY_FOREIGN}, null, null, null, null, null);


}

Where I View the Information

private void fillData() {
     // Get all of the rows from the database and create the item list

    mTimeNotesCursor = mDbHelper.fetchTime();
    startManagingCursor(mTimeNotesCursor);
   // startManagingCursor(mNotesCursor);

    // Create an array to specify the fields we want to display in the list (only TITLE)
    String[] from = new String[]{WWDatabase.KEY_HOUR,WWDatabase.KEY_FOREIGN};

    //String[] fromTime = new String[]{WWDatabase.KEY_HOUR};


    // and an array of the fields we want to bind those fields to (in this case just text1)
    int[] to = new int[]{R.id.textView2, R.id.textView3};

   //int[] toTime = new int[]{R.id.textView4};

    // Now create a simple cursor adapter and set it to display
    SimpleCursorAdapter notes = 
        new SimpleCursorAdapter(this, R.layout.time_text_row, mTimeNotesCursor, from, to);

    setListAdapter(notes);
   // SimpleCursorAdapter notesTime = 

       //new SimpleCursorAdapter(this, R.layout.time_text_row, mTimeNotesCursor, fromTime, toTime);
        //setListAdapter(notesTime);
}

I do not get any errors with this, But like I said it only shows the one table. All help is appreciated.

Please View [other Question][1] to help. [1]: stackoverflow.com/questions/8731603/… — Christian
– Christian, Commented Jan 4, 2012 at 18:00

Yaqub Ahmad · Accepted Answer · 2011-12-31 05:13:30Z

2

Use the rawQuery:

private final String MY_QUERY = "SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.other_id WHERE b.property_id=?";

OR

private final String MY_QUERY = "SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.other_id WHERE a.property_id=?";

db.rawQuery(MY_QUERY, new String[]{String.valueOf(propertyId)});

edited Dec 31, 2011 at 5:13

answered Dec 31, 2011 at 2:52

Yaqub Ahmad

27.7k24 gold badges105 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Yaqub Ahmad Over a year ago

property_id can be the Unique_Id of table_a or table_b based on your requirements OR property_id is the column on based of which you want to search the data

Christian Over a year ago

I am not sure if I am doing this right

private final String MY_QUERY = "SELECT * FROM wwTable mLat INNER JOIN wwTimeTable _id ON wwTable._id=wwTimeTable.mName WHERE wwTimeTable.mForeign=?";

return ourdb.rawQuery(MY_QUERY, new String[]{String.valueOf(KEY_FOREIGN)}); What am I doing wrong here?

Yaqub Ahmad Over a year ago

Can you provide your table structure LIke? Table1(TableId, column1, column2) Table2(Table2Id, TableId (AS FK), column2, column3)

Christian Over a year ago

Yes I have provided that under my tables at the top

Christian Over a year ago

Please View the other question that I have set up for more detail. stackoverflow.com/questions/8731603/…

Collectives™ on Stack Overflow

Android - How to retrieve data from multiple SQLite tables?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related