Two Table Query

Greetings-

I request your help constructing a query to return a list of active members based on 
the following:

CREATE TABLE Members(
  member_number int unsigned AUTO_INCREMENT,
  name          varchar(25),
  PRIMARY KEY(member_number))

CREATE TABLE Status(
  member_number int unsigned AUTO_INCREMENT,
  date          DATE,
  status        enum('Active', 'Inactive', 'Retired'))

So I might get:
Members:
1  Greg Lindstrom
2  Lee Ramsey
3  Don Tackett

Status:
1  2000/1/1  Active
2  2000/2/1  Active
1  2000/6/1  Inactive
3  2000/8/1  Active
1  2000/9/1  Active
2  2001/1/1  Retired

I would like the status table so I can calculate "time served", but I do not know how 
to construct a query to return all members with a given status for a given date. 

Example:  for 2000/7/1 and 'Active' 
 2 Lee Ramsey Active

and for 2001/2/1 and 'Active'
 1 Greg Lindstrom Active
 3 Don Tackett    Active

and 2001/2/1 'Retired'
 2  Lee Ramsey  Retired

 This would be possible to do in an external Python method (I'm working in Zope), but 
from what I've learned about SQL in the past 6 months, I'm thinking it could be done 
internally (non-corrolated join?).

Thanks for your help,
Greg Lindstrom
Vilonia, Arkansas (USA)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]