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

On Apr 22, 3:52 pm, Mtek <m...@mtekusa.com> wrote:
> 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

okay you have taken the first step in solving the problem. Just look
at the description of the problem you gave us. and try to do the next
step. Write the query 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)". Once you do that
you can do that, you can outer join that to the customer_account
table.

It can be done is SQL, and often such queries are more efficient in
SQL versus PL/SQL.

HTH,
Ed