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.
_____
>Dennis wrote:
I think you will need to use a sequence of insert statements as you have
shown above. Insert triggers won't work.
One further thing to note, you probably also need to store a reference to
the artist in the album table since multiple artists could have albums with
the same name ("Greatest Hits" comes to mind immediately).
Now the combination of the artist id and the album name must be unique.
This also means you will have to do the inserts into the artist table before
the insert into the album table, so that you have an artist id to assign to
the record in the album table.
"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,Artist
Text,unique(Artist));"
"CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL, ArtistId INTEGER,
Album Text,unique(ArtistId, Album));"
"CREATE TABLE BGM (BgmId INTEGER PRIMARY KEY NOT NULL,Bgm
Text,unique(Bgm));"
You should probably also normalize your playlist information into two
tables, one that store the name and id of each playlist, and one that
relates the playlist to the music table entries in that playlist.
"CREATE TABLE MUSIC (MusicId INTEGER PRIMARY KEY NOT NULL,Album_Id
INTEGER,Artist_Id INTEGER,Bgm_Id INTEGER, Track Text);"
"CREATE TABLE PLAYLIST(PlayListId INTEGER PRIMARY KEY NOT NULL, PlayListName
Text);"
"CREATE TABLE TRACKS(PlayListId INTEGER, MusicId INTEGER);"
You haven't said how you are accessing SQLite (i.e. C/C++ direct API calls
or some scripting language), but you can optimize your inserts if you use
the last_insert_id() function to retrieve and save the ids assigned as you
insert rows into the various tables. This will eliminate some unnecessary
select lookups.
"insert or ignore into ARTIST (Artist) values('Artist1');"
artist = sqlite3_last_insert_id();
"insert or ignore into ALBUM (Album, ArtistId) values('Album3', :artist);"
album = sqlite3_last_insert_id();
"insert or ignore into BGM (Bgm) values('Rock');"
bgm = sqlite3_last_insert_id();
"insert into MUSIC (Album_Id,Artist_Id,Bgm_Id) values(:album, :artist, :bgm,
'Track1.mp3');"
music = sqlite3_last_insert_id();
Now you can add this song to the playlist "New Music".
insert into table TRACKS values(
(select PlayListId from PLAYLIST where PlayListName = 'New Music'),
:music);
HTH
Dennis Cote
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
