The solution depends on which version of MySQL you are using. If you
are using 4.1, you the easiest solution is to use a sub-select.
Something like this may work:
SELECT m.member_number, m.name, s.status, s.date
FROM members AS m, status AS s ON
WHERE m.member_number = s.member_number
AND m.member_number NOT IN
(SELECT member_number FROM status WHERE status != 'Active' and date <=
'2000-07-01')
AND s.date <= '2000-07-01';
// This is not tested. I'm not running 4.1.
Otherwise you will need to use two queries like:
CREATE TEMPORARY TABLE inactive
SELECT member_number FROM status WHERE status != 'Active' and date <=
'2000-07-01';
SELECT m.member_number, m.name, s.status, s.date
FROM members AS m
JOIN status AS s ON m.member_number = s.member_number
LEFT JOIN inactive AS i ON m.member_number = i.member_number
WHERE i.member_number IS NULL
AND s.date <= '2000-07-01';
These statements produce:
mysql [test]> select * from inactive;
+---------------+------------+----------+
| member_number | date | status |
+---------------+------------+----------+
| 1 | 2000-06-01 | Inactive |
+---------------+------------+----------+
1 row in set (0.01 sec)
/// second select from above.
+---------------+------------+--------+------------+
| member_number | name | status | date |
+---------------+------------+--------+------------+
| 2 | Lee Ramsey | Active | 2000-02-01 |
+---------------+------------+--------+------------+
1 row in set (0.00 sec)
Of course you would need to change status and date to what ever you are
looking for.
Your create table statement for the Status table has "member_number int
unsigned AUTO_INCREMENT," you probably don't want the auto_increment on
the column.
I'm sure that if there is a better way, someone will point it out.
On Sun, 15 Jun 2003 07:44:24 -0500, [EMAIL PROTECTED] wrote:
> 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)
---
Listserv only address.
Jeff Shapiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
