Re: Help with Query

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

Relevant Pages

  • Re: [PHP] OOP slow -- am I an idiot?
    ... OOP has overhead. ... That saves you putting wasteful SQL queries in your ... If you need to do a complex query with a couple of joins and such, ... I want to create a "customer" class which fetches its attributes from a ...
    (php.general)
  • Sub Query Consolidation, Syntax Question
    ... I am new to writing free hand SQL, and have a two part question. ... #1 I'm trying to consolidate a number of queries into one query. ... to me that there should be a way to consolidate this into one place, ... Information] RIGHT JOIN NetShipPlusOpen ON [Customer Information].[Sold To ...
    (microsoft.public.access.queries)
  • Re: comboBox & northwinds sample orders form
    ... True, but OFTEN to display a customer name when you have a customer id, we ... dlookup) are suggest. ... the problem is that you don't need dlookup in a query. ... You can always just shove in the sql in place of the ...
    (microsoft.public.access.formscoding)
  • Re: Query going wrong
    ... statement from the Query. ... I am not a big programmer so I have no clue what it means when it's in SQL ... >> When I goto the Report Query Report it prints all the same information for ... >> Then when I am in a query where one customer has had the same service ...
    (microsoft.public.access.queries)
  • Re: SQL Syntax Help Please
    ... I would just have another Query that did the sums for the amount recieved ... The outer "layer" is your basic sql statement to get the total for the Order ... What makes this work for this grouping, so you have the Payments ... is the inner part that sums the customer payments based on the ...
    (microsoft.public.access.tablesdbdesign)