Re: Help with Query

On Apr 22, 3:52 pm, Mtek <m..._at_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 Received on Wed Apr 23 2008 - 07:56:44 CDT