Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
|
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 18th, 2003, 07:53 AM
Authorized User
 
Join Date: Oct 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default MySQL queries problem

I want to find the customer with the highest sales for each category.
Because i have to make seven queries to get the customer with the highest sales for each category by using the method below.
Instead of making two or more queries by changing the where c.cat_id in each queries, can i get the result using only one queries?
If yes, can someone please tell me how by correcting my code?

select c.cat_id, s.cust_id, cu.cust_fname, cu.cust_lname, sum((sd.sales_price * sd.quantity)) as Total
from product p, sales_detail sd, sales s, category c, brand b, customer cu
where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.cat_id=b.brand_cat and p.prod_brand = b.brand_id and s.cust_id=cu.cust_id and year(s.sales_date) between '1999' and '2003' and c.cat_id=1
group by s.cust_id
order by Total desc
limit 1;

select c.cat_id, s.cust_id, cu.cust_fname, cu.cust_lname, sum((sd.sales_price * sd.quantity)) as Total
from product p, sales_detail sd, sales s, category c, brand b, customer cu
where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.cat_id=b.brand_cat and p.prod_brand = b.brand_id and s.cust_id=cu.cust_id and year(s.sales_date) between '1999' and '2003' and c.cat_id=2
group by s.cust_id
order by Total desc
limit 1;

 
Old January 22nd, 2004, 07:15 AM
Registered User
 
Join Date: Jan 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you are executing sql from php then
You can generate the sql string dynamically through coding like following:

$sqlstr = "select c.cat_id, s.cust_id, cu.cust_fname, cu.cust_lname,
sum((sd.sales_price * sd.quantity)) as Total
from product p, sales_detail sd, sales s, category c, brand b, customer cu
where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.cat_id=b.brand_cat and p.prod_brand = b.brand_id and s.cust_id=cu.cust_id and year(s.sales_date) between '1999' and '2003' and c.cat_id = ";

$sqlstr .= $catid;

$sqlstr .= " group by s.cust_id
order by Total desc
limit 1";[/blue]





Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem while joining two queries yogeshyl SQL Language 0 July 21st, 2008 04:08 AM
problem with parameter queries ph0neman Classic ASP Basics 2 July 1st, 2008 08:57 PM
Combining Queries or results from 2 queries Ford SQL Server 2000 24 November 7th, 2005 08:54 PM
mySQL problem Henry 8 PHP How-To 4 August 2nd, 2004 12:32 PM
Select with multiple queries problem djbst84 SQL Language 0 May 21st, 2004 07:58 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.