Sreedhar.a wrote:
Hi Dennis,
I have created 2 tables for PlayList as u suggested as Follows.
"CREATE TABLE MUSIC (MusicId INTEGER PRIMARY KEY NOT NULL,Album_Id
INTEGER,Artist_Id INTEGER,Bgm_Id INTEGER, Track Text);"
MusicId Album_Id Artist_Id Bgm_Id Track
1 1 1 1
T1.mp3
2 1 1 2
T2.mp3
3 1 1 3
T3.mp3
4 2 2 1
S1.mp3
5 2 2 2
S2.mp3
6 2 2 1
S3.mp3
"CREATE TABLE PLAYLIST(PlayListId INTEGER PRIMARY KEY NOT NULL, PlayListName
Text);"
PlayListId PlayListName
1 PlayList1
2 PlayList2
"CREATE TABLE TRACKS(PlayListId INTEGER, MusicId INTEGER);"
PlayListId MusicId
1 1
2 4
1 2
2 6
1 3
1 6
My Doubt is:
If i want to list the MUSIC.Track for Playlist1.With the below statement i
could able to get only the First result. ie, T1.mp3
"SELECT Track from MUSIC where MUSIC.Id=(SELECT MusicId FROM TRACKS WHERE
TRACKS.PlayListId = (SELECT Id FROM PLAYLIST WHERE PlayListName ='Maha'));"
But my desired result is as follows.
T1.mp3
T2.mp3
T3.mp3
S3.mp3
Can u please correct where i am wrong.
You need to join the playlist, tracks, and music tables to do this.
Select Track from PLAYLIST
join TRACKS using PlayListId
join MUSIC using MusicId
where PlayListName = 'Maha';
Also, I realized after I posted my last message that using
last_insert_rowid is probably not a good idea. It will only return the
correct rowid when the insert or ignore actually does an insert. If the
row already exists in the table, it will return the wrong rowid. You
should stick with the original selects, unless you know that you are
inserting a new row.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
