Update Query with special conditions.

2004-11-24 Thread list 123. 
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 | -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 | -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]

Re: Update Query with special conditions.

2004-11-24 Thread GH 
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 | -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 | -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]
 


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