Re: Update Query with special conditions.

I am curious about doing something simular to this... does anyone have an id
ea


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 cha
nge the value of Participants.Active from Y to N is for three or more CONSEC
UTIVE sessions they have Attendance.Present = 'No'?
>
> The Attendance Table has Attendance.Session which coresponds to Sessions.S
essionID 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_incremen
t |
> | 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@gmail.com
>
>