Very Computer

[this is all commercial ingres 6.4]

I'm trying to help a non-profit (charity work here), and this is not
my strong card and is wasting a lot of time, I suspect someone here
can roll the answer to this one off the top of their head...

You have a typical member record, member id, name, address, phone, so
on.

Now you want to track renewals, and keep historical renewal
information on each person.

Later you want to create a view with the member record info and just
the latest renewal (e.g. tell me everyone who is expiring this month
and needs a renewal letter.)

My first thought was to make on member record table, and another table
with member_id,renewal_date and so you'd have one row for each of that
person's renewal, and then somehow you could select out the max date
(latest renewal) into a view, along with name and so on, and the rest
is easy (retrieve those records with a renewal more than 12 months
old.)

So something like (member_id is unique here):

        member_id       First   Last    Address
        0001            Bill    Clinton 1600 Penn Ave
        0002            John    Majors  10 Downing St

and (order isn't important here, member_id is not unique):

        member_id       renewal
        0001            2/1/91
        0001            2/1/92
        0002            1/1/92
        0002            1/1/91

and want to create a view (or even a new table, who cares?) like:

        member_id       First   Last    Address         Last_Renewal
        0001            Bill    Clinton 1600 Penn Ave   2/1/92
        0002            John    Majors  10 Downing St   1/1/92

but that last part (creating the view from the other two tables)
eludes me, am I missing something obvious? The max() function doesn't
help (as far as I can tell) cuz that just gets the max of the whole
column, not the max for a particular id (I guess being a programmer
hurts, I keep seeing this as a simple nested loop.)

Reorganizing the database to make this easier is no problem since I'm
just setting this up and I suspect I'm missing something very basic
anyhow. Should I just say the heck with it and copy the whole record
(row) every time a new renewal is entered and just keep one table? I
suppose that's one possibility, doesn't seem right tho.

--
        -Barry Shein

Purveyors to the Trade | Voice: 617-739-0202        | Login: 617-739-WRLD

"select t2.member_id, max(t2.renewal), t1.first, t1.last, t1.address
 from table1 t1, table2 t2
 where t2.member_id = t1.member_id
 group by t2.member_id, t1.first, t1.last, t1.address"

I don't work with SQL everyday so I'm sure someone will correct me where
I went wrong.

--tom

Thomas Lohman - Research Specialist/Software Engineer
MIT Building 36 - Room 297
Cambridge, MA. 02139

Phone: 617-258-6485