how to fetch data from more than one table

Question

I am new in android development.

I store a list of albums in a table that has these columns: albumid, albumname.

The song table has these columns: songid,song_title,tracksong_id, where the tracksong_id is a foreign key that refers to the albumid.

My create table query for the song table is:

create table album(id integer primary key autoincrement,album_name text)

create table song(song_id integer primary key autoincrement,song_name text not null,song_title text not null,tracksong_id integer,tracksong_id integer,FOREIGN KEY(tracksong_id) REFERENCES album(album_id))

For fetching all song details

public AlbumDTO getSOngById(long id)

{

AlbumDTO occasionDTO=null;

Cursor c=db.query(DATABASE_TABLE_SONG, new String[] {KEY_SONG_NAME,KEY_SONG_TITEL,TRACKSONGID},TRACKSONGID+"="+id,null,null,null,null);

if(c.moveToNext())

{

occasionDTO=new AlbumDTO();

//occasionDTO.song_id=c.getLong(c.getColumnIndex(KEY_SONG_ID));

occasionDTO.song_name=c.getString(c.getColumnIndex(KEY_SONG_NAME)).trim();

occasionDTO.song_title=c.getString(c.getColumnIndex(KEY_SONG_TITEL)).trim();

occasionDTO.tracksong_id=c.getLong(c.getColumnIndex(TRACKSONGID));

}

close();

return occasionDTO;

}

How can I fetch all data from both tables? One album can contain many songs.

Thanks for support

How you relate the both table you have to one common column in both table by that column you can get the data from both table. — Yog Guru

Hank Hank · Accepted Answer · 2012-02-06T05:25:55

Alright, this is what you want:

public static final String TABLE_SONG_JOIN_ALBUM = "song LEFT JOIN album ON sone. tracksong_id = album.id";
public static final String[] PROJECTION = new String[] {
    "song.song_name",
    "album.album_name"
    // maybe you want more field here
};

public AlbumDTO getSOngById(long id) {
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
    qb.setTables(TABLE_SONG_JOIN_ALBUM);
    String selection = "song.id = "+id;
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor c = qb.query(db, PROJECTION, selection, null, null, null, sortOrder, null);

    AlbumDTO occasionDTO=null;
    if(c.moveToNext()) {
        occasionDTO=new AlbumDTO();
        occasionDTO.song_id=c.getLong(c.getColumnIndex(KEY_SONG_ID));
        occasionDTO.song_name=c.getString(c.getColumnIndex(KEY_SONG_NAME)).trim();
        occasionDTO.song_title=c.getString(c.getColumnIndex(KEY_SONG_TITEL)).trim();
        occasionDTO.tracksong_id=c.getLong(c.getColumnIndex(TRACKSONGID));
    }

    db.close();
    return occasionDTO;
}

how to fetch data from more than one table

2 Answers