List:General Discussion« Previous MessageNext Message »
From:John Coder Date:December 5 2002 2:48am
Subject:Re: Table setup question
View as plain text  
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


Thread
Table setup questionBeauford.20032 Dec
  • Re: Table setup questionDL Neil2 Dec
  • RE: Table setup questionAdolfo Bello2 Dec
    • Re: Table setup questionBeauford.20034 Dec
      • RE: Table setup questionAdolfo Bello4 Dec
        • Re: Table setup questionBeauford.20034 Dec
      • Re: Table setup questionJohn Coder5 Dec
  • Re: Table setup questionDL Neil2 Dec
  • Re: Table setup questionBeauford.20032 Dec
    • RE: Table setup questionAdolfo Bello3 Dec
  • Re: Table setup questionDL Neil3 Dec
  • Select, mysql_fetch_array, PHP questionBeauford.20033 Dec
    • RE: Select, mysql_fetch_array, PHP questionJennifer Goodie3 Dec
    • Re: Select, mysql_fetch_array, PHP questionSteve Yates3 Dec
  • Re: Table setup questionBeauford.20033 Dec
  • Re: Select, mysql_fetch_array, PHP question (sorry - typo in my original email)Beauford.20034 Dec
Re: Select, mysql_fetch_array, PHP questionBeauford.20034 Dec