Sql query to find MAx row in subqury

Question

Hi All. These are my tables:User,Product,DiscountGroup,DiscountUser,DiscountProduct. DiscountProduct:

id    discountGroupId    productId     discount
---   --------------     ---------     -------
1        2                8             2000
2        3                8             1000
3        2                4              4500

DiscountUser:

id    discountGroupId    userId   
---   --------------     --------- 
1        2                2        
2        3                3        
3        2                2

DiscountGroup:

id    title   active
---   ------ --------     
1       A      1         
2       B      0       
3       C       1

I use Sql2000 . What I whant : first:for each productid and member find discountGroup that both of Them belong to it. I write my query:

 select * from discountGroup where id in
(select discountgroupId from discountproduct where productid=11)
 and id in 
(select discountgroupId from discountuser where userid=2)
 and  active=1

Secend:I want to find maximom discount for special product and member. How can i do it? Secend: for special user and all product I want to find the best discount and discountGroup Title: same this:

user produc discount discountGroup --- ----- ------- ------------ ali phone 400 A reeza mobile 200 B

Answer 1

Don't use subqueries, use joins:

select g.id, p.discount
from DiscountGroup g
inner join DiscountProduct p on p.discountGroupId = g.id
inner join DiscountUser u on u.discountGroupId = g.id
where p.productid = 11 and u.userid = 2

To get the maximum discount, use the max aggregate:

select max(p.discount)
from DiscountGroup g
inner join DiscountProduct p on p.discountGroupId = g.id
inner join DiscountUser u on u.discountGroupId = g.id
where p.productid = 11 and u.userid = 2