Re: INSERT SELECT

[Shaun]

Sorry guys,

Worked it out, if anyone is interested here is the query:

INSERT INTO Allocations(Project_ID, User_ID)
SELECT P.Project_ID, U.User_ID
FROM Users U, Projects P, Clients C
WHERE P.Client_ID = C.Client_ID
AND U.Client_ID = C.Client_ID
AND Project_ID = 2

""Shaun"" <shaunthornburgh@stripped> wrote in message 
news:20050912123622.4457.qmail@ style="color:#666">stripped...
> 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>
>
>