I am curious about doing something simular to this... does anyone have an idea
On Wed, 24 Nov 2004 00:43:32 -0500, list 123. <list> wrote:
> 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/mysql?unsub=garyhotko@xxxxxxxxx
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
