Re: "Lost in Select"

[Carlton Conley <cconley@CDG-INC.COM>]

there's probably lots of ways, but the first thing I thought of was

and a.member_id = (select max(member_id) from member
                                 where a.email = email)

-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Deborah DeCarvalho
Sent: Thursday, May 11, 2000 2:27 PM
To: SQR-USERS@list.iex.net
Subject: "Lost in Select"


Afternoon all -
I seem to be having a problem getting a very minor select to work and I have
no idea what I'm  missing - Now, I have been staring at this for well over 3
hours so I'm thinking I am either missing something so dumb that I'll end up
looking like an a**  or I'll leave immediately on vacation, but here goes..

I have 2 tables I need to query for a sweepstakes promotion. ( I said it was
minor !)
Since we have multiple sweepstakes on the web portals, I need my SQR to
select based on the group and on the date they registered on the portal -

I'm all set with the SQR prompting for the group and the dates, but here's
the problem, our code produces a "member_id" when a person registers on the
portal - They MUST use a unique member_name when registering otherwise,
they're told to try again - (member_name is a key field)
But, you can register as many times as you WANT as long as you use a
different member_name each time. Thats where my problem starts - I have
multiple people with the same email address but different user names - I
only want unique email names.
I was using this

select distinct(a.First_Name||','||a.Last_name||','||a.email) from member a,
group_member b
    where a.member_id = b.member_id
    and b.group_id='41'
    and a.email not like '%ibelong%'

But that doesn't work - I get duplicate email addresses
I also tried

     select a.email,a.member_name, b.member_since from member a,
group_member b where
     a.member_id=b.member_id
     and b.group_id='41' and
     b.member_since = (select max(b.member_since) from group_member b )

This HAS to be sooo simple, that I'll take my SQR manuals and throw them out
the window right?
Any suggestions??? (BTW, the group number is there for testing - it isn't
actually in the select - There is a prompt for that)

TIA

Deborah deCarvalho
Operations/Technology
IBelong Networks
www.ibelong.com
781.672.8349
mailto:ddecarvalho@ibelong.com