List:General Discussion« Previous MessageNext Message »
From:Mike Morton Date:August 25 2003 5:37pm
Subject:Multi-table query in one select - possible?
View as plain text  
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: 905-465-1263
* Email: mike@stripped
*
****************************************************

"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 

Thread
Multi-table query in one select - possible?Mike Morton25 Aug
  • Re: Multi-table query in one select - possible?Roger Baklund25 Aug