Re: Table setup question

[John Coder]

On Thu, 2002-12-05 at 14:42, Beauford.2003 wrote:
> 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 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 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).
> 

Try 
Select name from album join reference join songs where
album.ida=reference.ida and reference.ids=songs.ids and
songs.title='sometitle';

John Coder