Let's assume you want the entire albums list which the song
"Day and Night" by Billie Holiday is in.
SELECT t1.title,t1.artist,t3.name FROM sings t1
INNER JOIN reference t2 ON t1.ids=t2.ids
INNER JOIN album t3 ON t2.ida=t3.ida
WHERE t1.title='Day and Night' AND t1.artist='Billie Holiday'
(or "WHERE t1.ids=1" if you know the id of the song)
Relationship is like:
[songs] [album]
\\ //
[reference]
(this later one contains the list of songs of each album)
This way you treat songs and album separately and link them
through another table so you don't have any limit on the number
of albums a song can belong to.
If you know the id of a song all you have to do is
Adolfo
> -----Original Message-----
> From: Beauford.2003 [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, December 05, 2002 3:42 PM
> To: Adolfo Bello
> Cc: [EMAIL PROTECTED]
> Subject: Re: Table setup question
>
>
> Adolfo,
>
> Thanks for the info, but can you elaborate on it, 'cause
> basically I am just not getting the concept. No way no how
> can I get these joins to work.
>
> mysql> describe songs; (this contains an ID field and the
> title of the
> mysql> song
> and the artist.)
> +--------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +--------+-------------+------+-----+---------+-------+
> | ids | int(11) | | PRI | 0 | |
> | title | varchar(55) | YES | | NULL | |
> | artist | varchar(30) | YES | | NULL | |
> +--------+-------------+------+-----+---------+-------+
> 3 rows in set (0.00 sec)
>
> mysql> describe album; (this contains an ID field and the
> name of the
> mysql> album
> the above songs came from.)
> +-------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+-------------+------+-----+---------+-------+
> | ida | int(11) | | PRI | 0 | |
> | name | varchar(35) | YES | | NULL | |
> +-------+-------------+------+-----+---------+-------+
> 2 rows in set (0.00 sec)
>
> mysql> describe reference; (this contains the ID's that
> corrspond to the
> above two tables.)
> +-------+---------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+---------+------+-----+---------+-------+
> | ida | int(11) | | PRI | 0 | |
> | ids | int(11) | | PRI | 0 | |
> +-------+---------+------+-----+---------+-------+
> 2 rows in set (0.00 sec)
>
> This table is in this format: The left column corresponds to
> the ID of the ablum and the right field correspond to the ID
> of the song. From the example below, you can see that song #1
> appears on Albums 2, 3, 6, and 16.
>
> Song Album
> 1 2
> 1 3
> 1 6
> 1 15
> 2 7
> 2 21
> 2 23
>
> So with all this information, how would I perform a select
> that would show the all the albums a particular song were on.
> (from the example above what would I need to do to show the
> album name and song title for song ID # 1 above).
>
> Sorry if this is long, I just wanted to make sure there is
> enough info.
>
> TIA
>
> Beauford
>
> ----- Original Message -----
> From: "Adolfo Bello" <[EMAIL PROTECTED]>
> To: "'Beauford.2003'" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Monday, December 02, 2002 7:33 AM
> Subject: RE: Table setup question
>
>
> > 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
> >
> >
>
>
>
>
>
---------------------------------------------------------------------
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
