Help with Query

Hi,

Please take a look at this query:

SELECT max(action_date), customer_id
FROM email_product_hist
WHERE action = 'A'
  AND action_date BETWEEN TO_DATE('01012007','MMDDYYYY') AND TO_DATE('03312007','MMDDYYYY')
  AND email_product_id = 'PPM'
  AND customer_id NOT IN (SELECT customer_id FROM new_customer.customer_account)
GROUP BY customer_id;

It works. However, we have a table, CUSTOMER, which contains the customers email address. It is joined to the CUSTOMER_ACCOUNT table by the customer ID. It is also joined to the EMAIL_PRODUCT_HIST by customer_id.

What I am looking to do is get the email address from the CUSTOMER table for customers who are NOT in the CUSTOMER_ACCOUNT table, and, are IN the EMAIL_PRODUCT_HIST table that have the above criteria for the MAX date.

CUSTOMER_ID is unique in the CUSTOMER & CUSTOMER_ACCOUNT tables. It is NOT unique in the EMAIL_PRODUCT_HIST table. So, I'm looking to get the email, for the customer who has an action of 'A' and a email_product_id of 'PPM' for his max(action_date), and does not exist in the CUSTOMER_ACCOUNT table.

Hope that all makes sense. If this cannot be done, I can just write a block of code to do it.

Thank you,

John Received on Tue Apr 22 2008 - 15:52:43 CDT