CODEWITHSUNDEEP
androidsqlitefetch
arpit
arpit

Reputation: 555

how to fetch data from more than one table

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

Upvotes: 0

Views: 252

Answers (2)

Hank
Hank

Reputation: 1338

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;
}

Upvotes: 1

dilipkaklotar
dilipkaklotar

Reputation: 1469

table 1 col1 col2 col3

table 2 col1 col2 col3

if we want to select the one one column from both table then the following query is useful

"select a1.col1, a2.col1 from table1 a1, table2 a2 where ;"

here a1 and a2 are alias

Upvotes: 0

Related Questions