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
