fixing up this query

[TheTitans]

On my web page, I want to display a list of CDs that are associated with each article, and the songs that are associated with each CD.

 

To do this, I have three tables:

1. A table for the CDs

2. A table that has all the songs

3. A table that associates each CD with its song

 

Table cds has columns: id, article_id, name

Table songs has columns: id, name, lyrics

Table cd_songs has columns: id, cd_id, song_id

 

Now I have a the query that gets the CDs that are associated with each article.

$sql = "SELECT cds.id, cds.name, DATE_FORMAT(cds.release_date, '%M %e, %Y') AS formatted_date FROM cds WHERE cds.article_id = '" . $this->id . "'";

 

But I don't know how to modify the query so that it can also select all the songs for each CD. I'm not sure how to use the JOINs with this, so maybe someone can give me some assistance.

[pkedpker]

why not give all the songs in that table a id number to each CD in table for CD's like that you would need 2 tables not 3. Or some song can be on 2 cds? other then that I am horrible at my SQL i still haven't got my question solved 100%

[fenway]

Yes, JOINs would be required... what kind of output are you looking for?

[TheTitans]

Yes, JOINs would be required... what kind of output are you looking for?

 

The web page would show a list of the CDs. Under each CD would be the list of songs (just the name of the songs) that are associated with it. Each song would have a link to its own page to where the lyrics would be displayed. I know how to display the list of CDs. I just need some help with displaying the song names for each CD.

[fenway]

Then you need to join the cd table to the song table...