Normalization

[Anon]

I have three tables.
// prospect_table //
|+ prospectID + name + email + address +|
[+ 054050 + Peter+ [e-mail] + Bond Street+]
[ + 4541121 + John+ [e-mail] + High Street+ ]

// order_table //
|+ orderID + prospect + productID + quantity +|
[ 1 + 054050 + 12 + 1]
[ 1 + 054050 + 8 + 2]

// product_table //
|+ productID + name + price +|
[ 12 + Big Engine + 99.00 ]
[ 8 + Chair + 79.00 ]

What I want to do (now already knowing their prospectID is to select all productID from order_table where the prospect is prospectID. And the list all products from the product_table where productID is that found in order_table.

At the moment I do this:
SQL1 = "SELECT FROM order_table WHERE prospect = '$prospectID'"
while ($array =mysql_fetch_array(SQL1_result)) {
SQL2 = "select from product_table where productID = $array["productID"]"
}

How can I do this all in one SQL statement?? I.e. populate the SQLsearch though product_table using content from order_table??

[Anon]

You can process with a join:

SELECT product.
,order. ( OR anything you need from tables in from statement )
FROM product
,order
WHERE order.prospectid = '$prospectid'
AND product.productid = order.productid;

That's all !