Only way you are getting multiple is if when the customer comes, they have a
different E-mail, or you have multiple customers or visitors. If this is
the case which E-mail Address is correct?
If you use date visited and pick the max date, then you get the most recent
date. You have to figure out where the duplicates are and how to select the
correct rows. Since you are using cv.email then they must enter that when
they visit. They obviously visited more than once with had a different
E-mail on one occassion. Best guess is most recent E-Mail Address. Select
the row with the max date on the cv TABLE. You have to know your own data
better than us.
"DA Morgan" <damorgan_at_psoug.org> wrote in message
news:1173218763.588558_at_bubbleator.drizzle.com...
> amerar_at_iwc.net wrote:
>> On Mar 6, 1:04 pm, DA Morgan <damor..._at_psoug.org> wrote:
>>> ame..._at_iwc.net wrote:
>>>> Hi All,
>>>> I have this log-assed query. I need to make it such that I am getting
>>>> a distinct EMAIL value for that column. I'm not sure how to do that
>>>> with this query......it has no group by or anything...
>>>> select
>>>> cv.First_name,
>>>> cv.Last_name,
>>>> cv.email,
>>>> p.name as Product,
>>>> cp.Date_Entered as "Start Date",
>>>> cp.exp_date as "Expiration Date",
>>>> (cp.QUANTITY * p.base_price) as "Amount $"
>>>> from customer_product cp, customer_visitor cv, product p, TEMP_ADID_1
>>>> t
>>>> where cp.customer_id = t.customer_id
>>>> and cp.Date_Entered >= to_date('01.01.2000','mm/dd/rrrr')
>>>> and cp.Date_Entered < to_date('02.02.2007','mm/dd/rrrr')
>>>> and cp.customer_visitor_id = cv.customer_visitor_id
>>>> and cp.PRODUST_ID = p.PRODUST_ID;
>>>> Thanks in advance!!
>>> My first thought is SELECT DISTINCT but it would seem from what little
>>> you have provided that each person can have one, and only one, email
>>> address so please define DISTINCT as you are using it.
>>> --
>>> Daniel A. Morgan
>>> University of Washington
>>> damor..._at_x.washington.edu
>>> (replace x with u to respond)
>>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>>
>> Well, DISTINCT in the moddile of this query will not work. I am
>> looking to get one and only one of each email address, regardless of
>> the other values in the other columns......
>
> Then redesign your application. That is not what it was built to do.
>
> What you are indicating with your response is that a single customer_id
> with a single customer_vistor_id can point to multiple customers in
> your customer_visitor table. You will be fighting this nonsense for the
> rest of the application's life.
>
> The only alternative is to use MAX or MIN to grab one, and only one,
> email address but that a poor substitute for a good design.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Fri Mar 23 2007 - 13:05:29 CDT
