Multi-table query in one select - possible?

[Mike Morton]

Runnint Mysql 3.23.47

I have the following two tables:
backorder_notification
->product_code varchar
->email varchar
->date_added date
Products
->code varchar
->name varchar
->other stuff that is not important

Essencially - I want to get a count of the product codes that are in the
backorder_notification table ordered by count then by product name and
output the display:

Code Name Count

I am using PHP to do the connect, query and display.

Right now I have the following code:
$res=mysql_query("select distinct product_code from backorder_notifications
order by product_code");

while($row=mysql_fetch_array($res)) {
$pres=mysql_query("select name from products where
code='$row[product_code]'",$c)
$products=mysql_fetch_array($pres);
$cres=mysql_query("select count(product_code) as total from
backorder_notifications where product_code='$row[0]'",$c);
$counts=mysql_fetch_array($cres);

print "
$row[product_code]
$products[name]
$counts[total]";
}

And while this works, it sorts only by product code. I am looking for
help/advice on how to put this into one query so I only have to loop through
a result set once without doing all sorts of other connections.

As you can see I am using MYSQL 3 - so no union queries - and I cannot
upgrade the database since it is on a host provider.

Any assistance is appreciated!

TIA

--
Cheers

Mike Morton

************************************************** **
*
* Tel: [phone]
* Email: mi**@webtraxx.com
*
************************************************** **

"Indeed, it would not be an exaggeration to describe the history of the
computer industry for the past decade as a massive effort to keep up with
Apple."
- Byte Magazine

Given infinite time, 100 monkeys could type out the complete works of
Shakespeare. Win 98 source code? Eight monkeys, five minutes.
-- NullGrey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw