SELECT query that JOINs four tables

[hawlast]

I have created four tables in a MySQL database
town (town_id, town_name)
area ( area_id, area_name, town_id)
station (sta_id, sta_name)
prices (pid, dollar, sta_name, area_id)

A: I want to get a list of 10 distinct towns/cities, each with a corresponding station where the price in dollars is the cheapest.
For example:

1. Melbourne, Airport, $20
2. Nairobi, JKIA, $50

B: Suppose am given a town or city say Melbourne. I want to get a list of 10 distinct areas (in this town) where price is cheapest.
City: Melbourne

1. Airport - $ 20
2. Southbank - $ 23
3. Windsor -$ 24.32

Thank you in advance.

[guido2004]

What have you got so far?

[hawlast]

What have you got so far?

For A above, I am using the query below, the problem is that some towns appear several times.

<?php
$query = "SELECT town.town_name, station.sta_name, prices.dollar FROM town,station,prices,area WHERE prices.sta_name = station.sta_name AND prices.sta_name=station.sta_name AND area.town_id=town.town_id ORDER BY prices.dollar ASC LIMIT 10";
$result = @mysql_query($query,$db) or die ("Error in query: $query. " . mysql_error());
echo "<ul class=\"list\">";
while (list($town_name,$sta_name,$dollar) = mysql_fetch_row($result))
{
echo "<li><a href=\"./?town_name=$town_name\"><b>$town_name - $sta_name</b><br />Ksh. $dollar</a></li>";  
}
echo "</ul>"; 
mysql_free_result($result);
?>

For B above I am running the query below, it is not giving the correct dollar for a given station:

<?php  
if (!isset($_REQUEST['town_name'])){$town = 'Nairobi';} else {$town = $_REQUEST['town_name'];}
?>
<div class="content"><h3><?php echo $town ?></h3>
<?php
$query = "SELECT area.area_name, station.sta_name, prices.dollar FROM area, station,prices, town WHERE prices.area_id IN (SELECT DISTINCT area_id FROM prices) AND prices.sta_name = station.sta_name AND area.town_id=town.town_id AND town.town_name = '$town' ORDER BY dollar ASC LIMIT 10";
$result = @mysql_query($query,$db) or die ("Error in query: $query. " . mysql_error());
echo "<ul class=\"list\">";
while (list($area_name,$sta_name,$dollar) = mysql_fetch_row($result))
{
echo "<li><a href=\"area.php?area_name=$area_name\"><b>$area_name - $sta_name</b><br />Ksh. $dollar</a></li>";  
}
echo "</ul>"; 
mysql_free_result($result);
?>