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
|
