INSERT SELECT

[Shaun]

Hi,

I have four tables: Projects, Users, Allocations and Clients. A Client will 
have many projects and many Users, Users are allocated to Projects via a 
link table - Allocations. When I add a project I want to have an option of 
allocating all users to the project, can this be done with a select insert 
statement?

Thanks for your help

Here are my table definitions:

mysql> DESC Users;
+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra 
|
+----------------------+--------------+------+-----+---------+----------------+
| User_ID              | int(11)      |      | PRI | NULL    | 
auto_increment |
| Client_ID            | int(3)       | YES  |     | NULL    | 
|
| User_Username        | varchar(40)  |      |     |         | 
|
| User_Firstname       | varchar(50)  | YES  |     | NULL    | 
|
| User_Lastname        | varchar(50)  | YES  |     | NULL    | 
|
| User_Password        | varchar(20)  | YES  |     | NULL    | 
|
| User_Type            | varchar(20)  |      |     | Nurse   | 
|
| User_Email           | varchar(100) | YES  |     | NULL    | 
|
| User_Manager_Email   | varchar(100) | YES  |     | NULL    | 
|
| User_Manager_Email_2 | varchar(100) | YES  |     | NULL    | 
|
| User_Manager_Email_3 | varchar(100) | YES  |     | NULL    | 
|
| User_Manager_Email_4 | varchar(100) | YES  |     | NULL    | 
|
| User_Manager_Email_5 | varchar(100) | YES  |     | NULL    | 
|
| User_Manager_Email_6 | varchar(100) | YES  |     | NULL    | 
|
| User_Location        | varchar(40)  | YES  |     | NULL    | 
|
| Company              | varchar(100) | YES  |     | NULL    | 
|
| Address_Line_1       | varchar(100) | YES  |     | NULL    | 
|
| Address_Line_2       | varchar(100) | YES  |     | NULL    | 
|
| Town                 | varchar(100) | YES  |     | NULL    | 
|
| County               | varchar(100) | YES  |     | NULL    | 
|
| Postcode             | varchar(100) | YES  |     | NULL    | 
|
| Country              | varchar(100) | YES  |     | NULL    | 
|
| Mobile_Number        | varchar(20)  | YES  |     | NULL    | 
|
| Rep_Nurse_1          | int(11)      | YES  |     | NULL    | 
|
| Rep_Nurse_2          | int(11)      | YES  |     | NULL    | 
|
| Approver1_ID         | int(11)      | YES  |     | NULL    | 
|
| Approver2_ID         | int(11)      | YES  |     | NULL    | 
|
| Booking_Credits      | int(11)      | YES  |     | NULL    | 
|
| Status               | varchar(50)  |      |     | Active  | 
|
| Location_ID          | int(11)      | YES  |     | NULL    | 
|
+----------------------+--------------+------+-----+---------+----------------+
30 rows in set (0.01 sec)

mysql> DESC Projects;
+----------------------------+--------------+------+-----+---------+----------------+
| Field                      | Type         | Null | Key | Default | Extra 
|
+----------------------------+--------------+------+-----+---------+----------------+
| Project_ID                 | int(11)      |      | PRI | NULL    | 
auto_increment |
| Project_Name               | varchar(100) |      |     |         | 
|
| Client_ID                  | int(11)      |      |     | 0       | 
|
| Rep_Viewable               | char(3)      |      |     | Yes     | 
|
| Administrator_ID           | int(11)      | YES  |     | NULL    | 
|
| Administrator_Phone_Number | varchar(20)  | YES  |     | NULL    | 
|
| Project_Manager_ID_1       | int(11)      | YES  |     | NULL    | 
|
| Project_Manager_ID_2       | int(11)      | YES  |     | NULL    | 
|
+----------------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> DESC Clients;
+------------------------------------+--------------+------+-----+---------+----------------+
| Field                              | Type         | Null | Key | Default | 
Extra          |
+------------------------------------+--------------+------+-----+---------+----------------+
| Client_ID                          | int(11)      |      | PRI | NULL    | 
auto_increment |
| Client_Name                        | varchar(100) | YES  |     | NULL    | 
|
| Client_Type                        | varchar(20)  | YES  |     | NULL    | 
|
| Group_Start_Location_Code          | tinyint(4)   | YES  |     | NULL    | 
|
| Group_Length_Location_Code         | tinyint(4)   | YES  |     | NULL    | 
|
| Business_Unit_Start_Location_Code  | tinyint(4)   | YES  |     | NULL    | 
|
| Business_Unit_Length_Location_Code | tinyint(4)   | YES  |     | NULL    | 
|
| Region_Start_Location_Code         | tinyint(4)   | YES  |     | NULL    | 
|
| Region_Length_Location_Code        | tinyint(4)   | YES  |     | NULL    | 
|
+------------------------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> DESC Allocations;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| Project_ID | int(11) |      | PRI | 0       |       |
| User_ID    | int(11) |      | PRI | 0       |       |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>