SQL or QUEL db design question...

SQL or QUEL db design question...

Post by Barry She » Thu, 04 Mar 1993 12:40:28



[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

 
 
 

SQL or QUEL db design question...

Post by Thomas J Lohm » Fri, 05 Mar 1993 00:17:17



>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

Given the above set up, I think you can get the the info you're
looking for by doing:

"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


 
 
 

1. Sql server, db design question

Hello All,
We presently have an Access db for our server inventory
purposes. With the large number of people trying to
connect to the db, it has reached its limits. I am trying
to port that db to a Sql Server 2000 Enterprise box. The
db was never really normalized.
So my question is
1.do I create a new db in Sql server, normalize the tables
then add the data?
2.Or do I just the dump the access db into a new database
via DTS and then manipulate the tables? If so how do I go
about it?
Time is of the essence, so I am looking for the fastest
way to do it. However I want to do this right.
There are approximately 75 tables.  
Any other questions? Please e-mail me.

2. tricky sql statement

3. Ingres QUEL/EQUEL Replace Question

4. What Java IDE?

5. DB Design Question

6. Importing Text Files Into Access 97

7. SQLXMLOLEDB ClientSideXML From C++ Using OLE DB NOT ADO

8. DB Design question

9. DB Design Question

10. DB Design Question/Help

11. DB Design Question

12. a newbie db design question...