Dear all,

I have 2 tables product_customer and product_price

product_customer

prod_cust_id    prod_cust_product_id    prod_cust_customer_id
1               1                       1
2               2                       1
3               3                       1
4               1                       2

product_price

prod_price_id   prod_price_prod_cust_id prod_price_unitprice    prod_price_currency_id 
 prod_price_effective_date
1               1                       1.23456                 2                      
 6/30/2003
2               1                       1.12346                 3                      
 5/9/2003
3               4                       0.23456                 3                      
 5/21/2003
4               4                       1.45678                 3                      
 6/26/2003

Column prod_price_prod_cust_id of product_price table references prod_cust_id of product_customer table..

I have this query :

SELECT prod_price_prod_cust_id, prod_price_id, prod_price_unitprice, prod_price_effective_date
FROM product_price
WHERE prod_price_prod_cust_id in (1,4)
GROUP BY prod_price_prod_cust_id, prod_price_id, prod_price_unitprice, prod_price_effective_date
HAVING MAX(prod_price_effective_date) <= current_date


Which returns :

prod_price_prod_cust_id prod_price_id   prod_price_unitprice    
prod_price_effective_date
1                       2               1.12346                 5/9/2003
4                       3               0.23456                 5/21/2003
4                       4               1.45678                 6/26/2003

What I intend to get is the maximum prod_price_effective_date for each prod_price_prod_cust_id.
The second row should not be included in the output since 6/26/2003 is greater than 5/21/2003 .


My target output is this :

prod_price_prod_cust_id prod_price_id   prod_price_unitprice    
prod_price_effective_date
1                       2               1.12346                 5/9/2003
4                       4               1.45678                 6/26/2003

Any suggestions on how I can arrive with these output??

Help is greatly appreciated..TIA!

Marie Gezeala M. Bacuņo II
Information Systems Department

Your choice: the red pill or the blue pill.

_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail



-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php



Reply via email to