multiple tables/queries

[admin]

Hey,

Got some code that I know could be more efficient. I basically need to list composers and their compositions after their name. In my following code, I generate a mysql query per composer to get their compositions from another table. I have linked tables before in a single query, and I know that is probably what I need here. What I can't get my head round is how to link one row in one table to many in another, can anyone help?

______________
$comp_list_sql = "SELECT c_id, firstname, lastname FROM cm2k_composers ORDER BY lastname";
$comp_list_result = @mysql_query($comp_list_sql,$connection) or die("Couldn't execute query.");

while ($comp_list_row = mysql_fetch_array($comp_list_result))
{
$firstname = $comp_list_row['firstname'];
$lastname = $comp_list_row['lastname'];
$c_id = $comp_list_row['c_id'];

$comp_list_display_block .= "$lastname, $firstname <A HREF=http://www.phpbuilder.com/board/archive/index.php/\"composer.php?c_id=$c_id\">Details..</A>
";

$piece_list_sql = "SELECT p_id, p_name FROM cm2k_pieces WHERE rel_c_id = $c_id ORDER BY p_name";
$piece_list_result = @mysql_query($piece_list_sql,$connection) or die("Couldn't execute query.");

$comp_list_display_block .= "<UL>";
while ($piece_list_row = mysql_fetch_array($piece_list_result))
{
$p_id = $piece_list_row[p_id];
$p_name = $piece_list_row['p_name'];

$comp_list_display_block .= "<LI><A HREF=http://www.phpbuilder.com/board/archive/index.php/\"piece.php?p_id=$p_id\">$p_name</A></LI>
";
}
$comp_list_display_block .= "</UL>
";
}
_________
Help appreciated,

Ben