Try something like
CREATE TABLE album( ida int primary key , title varchar(n) not null ) ;
CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ;
CREATE TABLE albumsongs(
ida int not null,
ids int not null,
primary key(ida,ids),
foreign key(ida) references album(ida),
foreign key(ids) references songs(ids));
Querying for albums which have a songs is something like:
SELECT title,song FROM album INNER JOIN albumsongs ON ...
INNER JOIN songs ON ... WHERE song='your song name';
> -----Original Message-----
> From: Beauford.2003 [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, December 01, 2002 9:59 PM
> To: [EMAIL PROTECTED]
> Subject: Table setup question
>
>
> Hi,
>
> I have a database of albums by a group and I want to be able
> to search on this table to find out what songs are duplicated
> on what albums (there are
> 36+ albums). The problem is how do I set up the database.
>
> Example:
>
> Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song
> Album2 has 4 songs. 4.song, 5.song, 6.song, 3.song
> Album3 has 4 songs. 7.song, 8.song, 1.song, 3.song
>
> So 3.song appears on all 3 albums.
>
> Currently I have it set up with two tables as shown below,
> but I am thinking there has to be a better way to do this
> than to duplicate the name of the song three, four, or five
> times in the table.
>
> Table AlbumName
>
> Album ID
> ==== ==
> Album1 1
> Album2 2
> Album3 3
>
> Table SongTitle
>
> Song ID
> === ==
> 3.song 1
> 3.song 2
> 3.song 3
> 7.song 3
> etc.
> etc.
>
> So basically my search is - SELECT Album, Song FROM
> AlbumName, SongTitle WHERE AlbumName.ID=SongTitle.ID;
>
> Given the setup above, is there a way that I can put in the
> SongTitle.ID field that song appears on more than one album.
> Maybe something like:
>
> Song ID
> === ==
> 3.song 1, 2, 3
>
> But then what would my search be.
>
> Sorry for the length of this, but I am learning MySQL and
> trying to get a handle on all of it. My way works, but I'm
> sure there has to be a better way.
>
> Any thoughts are appreciated.
>
> TIA, Beauford
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
