[snip] I have four tables among others in my database: Bookings, Work_Types, Practices & Projects. Bookings occur in a Practice for a Project and have a Work_Type. A Practice can have many Bookings but must have one and only one Booking where the Work_Type.Day_Type = 1. This rule was introduced after the system was initially set up and I have a feeling there may be Practices that have no Day 1's. So how can I perform a query that returns all practices
that have had bookings but no Day 1's for a particualar project? Please see table definitions below. [/snip] Thanks very much for your help. mysql> desc Bookings; | Booking_ID | int(11) | | PRI | | Booking_Type | varchar(15) | | | | User_ID | int(11) | | | 0 | Project_ID | int(11) | YES | | | Rep_ID | int(11) | YES | | | Practice_ID | int(11) | YES | | | Booking_Creator_ID | int(11) | YES | | | Booking_Creation_Date | datetime | YES | | | Booking_Start_Date | datetime | | | | Booking_End_Date | datetime | | | | Booking_Completion_Date | date | YES | | | Booking_Mileage | int(5) | YES | | | Booking_Status | varchar(15) | | | | Unavailability_ID | int(2) | YES | | | Task_ID | int(11) | YES | | | Work_Type_ID | int(2) | YES | | | Additional_Notes | text | YES | | | Pre_Event_Copy_Received_By_Scheduling | char(3) | YES | | | Post_Event_Original_Completed_Form_Received | char(3) | YES | | | Section_C | char(3) | YES | | | Date_Difference | varchar(20) | | | | AU_Booking_ID | int(11) | YES | | mysql> desc Projects; | Project_ID | int(11) | | PRI | NULL | | 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) | | | | | Project_Manager_ID_1 | int(11) | YES | | NULL | | Project_Manager_ID_2 | int(11) | YES | | NULL | mysql> desc Practices; | Practice_ID | int(11) | | PRI | NULL | auto_increment | PCT_ID | int(11) | | | 0 | | Practice_Name | varchar(40) | | | | | Practice_Address | varchar(255) | | | | | Practice_Postcode | varchar(10) | | | | | Practice_Telephone | varchar(15) | | | | | Practice_Manager | varchar(40) | | | | | Practice_Lead_GP | varchar(40) | | | | | Practice_List_Size | int(11) | YES | | NULL | | Practice_System | varchar(100) | | | | | NHS_ID | varchar(20) | YES | | NULL | | MiQuest | char(3) | YES | | NULL | mysql> desc Work_Types; | Work_Type_ID | int(3) | | PRI | NULL | auto_increment | | Project_ID | int(11) | YES | | NULL | | | Day_Type | int(2) | YES | | NULL | | | Work_Type | varchar(40) | | | | | Not including all the possible columns, just an untested skeleton....and pure speculation without seeing a proposed result set.... SELECT p.Practice_ID, w.Day_Type FROM Practices p LEFT OUTER JOIN Bookings b ON(p.Practice_ID = b.Practice_ID) LEFT OUTER JOIN WorkTypes w ON(b.Project_ID = w.Project_ID) WHERE b.Practice_ID IS NOT NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
