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"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
