Using mySQL 4.0, I would like to know how I can code a query that will change
the value of Participants.Active from Y to N is for three or more CONSECUTIVE
sessions they have Attendance.Present = 'No'?
The Attendance Table has Attendance.Session which coresponds to
Sessions.SessionID and Attendance.Participant coresponds to
Participants.Part_ID;
To assist, I have shown you the data of the Sessions and the descriptions of
Attendance, Participants, Attendance
Thanks
G
mysql> describe Participants;
+-----------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+---------+----------------+
| Part_ID | smallint(10) unsigned | | PRI | NULL | auto_increment |
| LastName | varchar(30) | | PRI | | |
| FirstName | varchar(30) | | PRI | | |
| DOB | date | YES | | NULL | |
| Sex | enum('M','F') | | | M | |
| Phone1 | varchar(12) | | MUL | | |
| Phone2 | varchar(12) | YES | | NULL | |
| Notes | text | | | | |
| Facesheet | enum('Have','Need') | | | Need | |
| Active | set('Y','N') | | | Y | |
+-----------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
mysql> describe Attendance;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| AttID | int(4) | | PRI | NULL | auto_increment |
| Session | int(2) | | MUL | 0 | |
| Participant | int(2) | | | 0 | |
| Present | enum('Yes','No') | | | Yes | |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> describe Sessions;
+-------------+-----------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+------+-----+------------+----------------+
| SessionID | int(2) unsigned | | PRI | NULL | auto_increment |
| SessionDate | date | | PRI | 0000-00-00 | |
+-------------+-----------------+------+-----+------------+----------------+
2 rows in set (0.03 sec)
mysql> select * from Sessions;
+-----------+-------------+
| SessionID | SessionDate |
+-----------+-------------+
| 1 | 2004-10-30 |
| 2 | 2004-11-06 |
| 3 | 2004-11-13 |
| 4 | 2004-11-20 |
| 5 | 2004-12-04 |
| 6 | 2004-12-11 |
| 7 | 2005-01-08 |
| 8 | 0000-00-00 |
| 9 | 2005-01-29 |
| 10 | 2005-02-05 |
| 11 | 2005-02-12 |
| 12 | 2005-02-26 |
| 13 | 2005-03-05 |
| 14 | 2005-03-12 |
| 15 | 2005-03-19 |
| 16 | 2005-04-02 |
| 17 | 2005-04-09 |
| 18 | 2005-04-16 |
| 19 | 2005-04-23 |
| 20 | 2005-05-07 |
| 21 | 2005-05-14 |
| 22 | 2005-05-21 |
+-----------+-------------+
22 rows in set (0.05 sec)
+-----------------+
| Tables_in_AHRC |
+-----------------+
| Attendance |
| Participants |
| ProgressNotes |
| Sessions |
| Staff |
| StaffAttendance |
+-----------------+
6 rows in set (0.00 sec)
________________________________________________________________
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
