select from 2 tables using results from 1st table

[aussieguy]

I have two tables
table 1)
columns are ---- id | tourcode | tourname

This table is a list of tours that are available.

table 2) is a "sales table listing the sales of any of the tours
columns are --- id | tour_date | tour_code | tour_name | seats

I want to be able to list each tour from table 1

Then using the tourcode found from the first table I want to look-up how many seats were booked (from the second table)

so far I have got this much to work

to make a list of the tours in table 1
-----------code-------------------------
$query2 = "SELECT tourcode, tourname FROM info_tours WHERE id > 0 order by tourcode ";
$result = mysql_query($query2) or die ("<br>can not do query2");

while ($row =mysql_fetch_array($result))

{ extract ($row);

echo "$tourcode $tourname<br>";

}

------------------end code example-------------------

how would I change the above code so that for each $tourcode found in table 1, I can sum up the sales ($seats) in the second table

$query3 = "SELECT SUM(seats) as booked FROM customer_tours WHERE tour_code= '$tourcode' ";

the result would be

$tourcode $tourname has sold $seats seats<br>

TN-1 Tourname1 has sold 12 seats
TN-2 Tourname2 has sold 15 seats
TN-3 Tourname3 has sold 10 seats

etc etc

I have looked everywhere for examples on how to do this but I just can't get it to work
Any help much appreciated....

Thanks in advance

[liquorvicar]

you need to write a query joining the two tables. something like this:

$query2 = "SELECT t1.tourcode, t1.tourname,SUM(seats) FROM info_tours t1,table2 t2 WHERE t1.tourcode=t2.tourcode and id > 0 group by tourcode order by tourcode ";
$result = mysql_query($query2) or die ("<br>can not do query2");

while ($row =mysql_fetch_array($result))

{ extract ($row);

echo "$tourcode $tournamehas sold $seats seats<br>";

}