Re: Multi-table query in one select - possible?

2003-08-25 Thread Roger Baklund 
* 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:
> 
> CodeNameCount

You can join them all, group, count and order in the same query:

SELECT 
product_code, 
name, 
count(product_code) as total  
  FROM backorder_notifications,products
  WHERE
backorder_notifications.product_code = products.code
  GROUP BY product_code, name
  ORDER BY total

HTH,

-- 
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Multi-table query in one select - possible?

2003-08-25 Thread 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:

CodeNameCount

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: [EMAIL PROTECTED]
*


"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/[EMAIL PROTECTED]