Mysql queries problem

[hosefo81]

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;

[Tmic]

can\'t test it but maybe if you change

c.cat_id = 1

to

c.cat_id in (1,2,3,..)