Re: How can I find this data?
Hi Shawn, Thanks for your reply. The query you suggested seems to return all practices that have bookings where the day type does not equal '1' - I changed your query so it searches on the day_type rather than work_type as this is what is required: SELECT p.Practice_ID ,p.Practice_Name ,b.Booking_ID ,wt.Project_ID ,SUM(If(wt.Day_Type=1,1,0)) as WorkType_1 ,count(1) as TotalWorkType FROM Practices p INNER JOIN Bookings b ON b.Practice_ID = p.Practice_ID INNER JOIN Work_Types wt ON wt.Work_Type_ID = b.Work_Type_ID GROUP BY 1,2,3,4 HAVING WorkType_1=0; Let me give you an example. Your query returns this in the last row: +-+--++++---+ | Practice_ID | Practice_Name| Booking_ID | Project_ID | WorkType_1 | TotalWorkType | +-+--++++---+ | 11094 | Bugbrooke Surgery| 7114 | 12 | 0 | 1 | +-+--++++---+ But if i select all bookings where the practice_id = 11094 i get another booking where the day_type = 1: mysql SELECT * FROM Bookings WHERE Practice_ID = 11094; ++--+-+++-++---+-+-+-+-++---+-+--++---+-+---+-+---+ | Booking_ID | Booking_Type | User_ID | Project_ID | Rep_ID | Practice_ID | Booking_Creator_ID | Booking_Creation_Date | Booking_Start_Date | Booking_End_Date| Booking_Completion_Date | Booking_Mileage | Booking_Status | Unavailability_ID | Task_ID | Work_Type_ID | Additional_Notes | Pre_Event_Copy_Received_By_Scheduling | Post_Event_Original_Completed_Form_Received | Section_C | Date_Difference | AU_Booking_ID | ++--+-+++-++---+-+-+-+-++---+-+--++---+-+---+-+---+ | 7112 | Booking | 52 | 12 |638 | 11094 | 1174 | 2005-04-19 12:31:51 | 2005-05-04 09:30:00 | 2005-05-04 18:00:00 | NULL| 0 | Incomplete | NULL | 7 | 22 | x | No | No | No| n/a | NULL | | 7114 | Booking |1601 | 12 |638 | 11094 | 1174 | 2005-04-19 12:57:53 | 2005-05-25 09:30:00 | 2005-05-25 18:00:00 | NULL| 0 | Incomplete | NULL | 7 | 24 | x | No | No | No| n/a | NULL | ++--+-+++-++---+-+-+-+-++---+-+--++---+-+---+-+---+ 2 rows in set (0.02 sec) mysql SELECT * FROM Work_Types WHERE Project_ID = 12; +--++--+---+ | Work_Type_ID | Project_ID | Day_Type | Work_Type | +--++--+---+ | 22 | 12 |1 | Day 1 | | 24 | 12 |2 | Day 2 | +--++--+---+ 2 rows in set (0.00 sec) mysql Do you see my problem here?! Any advice would be greatly appreciated... Shaun From: [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: How can I find this data? Date: Wed, 4 May 2005 09:42:47 -0400 shaun thornburgh [EMAIL PROTECTED] wrote on 05/04/2005 06:54:23 AM: Hi, I have four tables among others in my database: Bookings, Work_Types, Practices Projects. Bookings occur
RE: How can I find this data?
[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 skeletonand 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]
Re: How can I find this data?
shaun thornburgh [EMAIL PROTECTED] wrote on 05/04/2005 06:54:23 AM: Hi, 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. Thanks very much for your help. mysql desc Bookings; +-+-+-- +-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+-- +-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Rep_ID | int(11) | YES | | NULL|| | Practice_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Unavailability_ID | int(2) | YES | | NULL|| | Task_ID | int(11) | YES | | NULL|| | Work_Type_ID| int(2) | YES | | NULL|| | Additional_Notes| text| YES | | NULL|| | Pre_Event_Copy_Received_By_Scheduling | char(3) | YES | | NULL|| | Post_Event_Original_Completed_Form_Received | char(3) | YES | | NULL|| | Section_C | char(3) | YES | | NULL|| | Date_Difference | varchar(20) | | | n/a || | AU_Booking_ID | int(11) | YES | | NULL|| +-+-+-- +-+-++ 22 rows in set (0.00 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) | | | | | | 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 Practices; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra |
RE: How can I find this data?
Hi Jay, Thanks for your reply, I tried your query but it jus hang :( Here is some sample data: mysql SELECT * FROM Bookings WHERE Practice_ID = 11049; ++--+-+++-++---+-+-+-+-++---+-+--+--+---+-+---+-+---+ | Booking_ID | Booking_Type | User_ID | Project_ID | Rep_ID | Practice_ID | Booking_Creator_ID | Booking_Creation_Date | Booking_Start_Date | Booking_End_Date| Booking_Completion_Date | Booking_Mileage | Booking_Status | Unavailability_ID | Task_ID | Work_Type_ID | Additional_Notes | Pre_Event_Copy_Received_By_Scheduling | Post_Event_Original_Completed_Form_Received | Section_C | Date_Difference | AU_Booking_ID | ++--+-+++-++---+-+-+-+-++---+-+--+--+---+-+---+-+---+ | 6148 | Booking |1571 | 32 | 1629 | 11049 | 75 | 2005-03-11 13:29:40 | 2005-04-01 09:30:00 | 2005-04-01 17:30:00 | NULL| 0 | Incomplete | NULL | 26 | 76 | x| Yes | NULL | No| n/a | NULL | | 6149 | Booking |1571 | 32 | 1629 | 11049 | 75 | 2005-03-11 13:30:49 | 2005-05-06 09:30:00 | 2005-05-06 17:30:00 | NULL|NULL | Incomplete | NULL | 26 | 77 | x| 0 | NULL | 0 | n/a | NULL | | 50 | Booking |1571 | 3 | 1629 | 11049 | 75 | 2005-03-11 13:31:20 | 2005-05-09 09:30:00 | 2005-05-09 17:30:00 | NULL|NULL | Incomplete | NULL | 26 |9 | x| 0 | NULL | 0 | n/a | NULL | | 55 | Booking |1645 | 3 | 1629 | 11049 | 75 | 2005-04-01 11:38:24 | 2005-04-04 09:30:00 | 2005-04-04 17:30:00 | NULL| 0 | Incomplete | NULL | 26 |9 | x| No | NULL | No| n/a | NULL | ++--+-+++-++---+-+-+-+-++---+-+--+--+---+-+---+-+---+ 4 rows in set (0.02 sec) mysql SELECT * FROM Work_Types WHERE Project_ID = 32; +--++--++ | Work_Type_ID | Project_ID | Day_Type | Work_Type | +--++--++ | 76 | 32 |1 | Day 1 | | 77 | 32 |2 | Day 2 | +--++--++ 2 rows in set (0.00 sec) mysql SELECT * FROM Work_Types WHERE Project_ID = 3; +--++--++ | Work_Type_ID | Project_ID | Day_Type | Work_Type | +--++--++ |8 | 3 |1 | Day 1 | |9 | 3 |2 | Day 2 | +--++--++ 2 rows in set (0.00 sec) mysql Here you can see that Practice 11049 has four bookings. The two for project 32 are ok, but the two for project 3 do not have a booking where the day type is 1. I hope this makes sense! Thanks again for your help. Shaun From: Jay Blanchard [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: How can I find this data? Date: Wed, 4 May 2005 06:36:18 -0500 [snip] I have four tables among others in my database: Bookings, Work_Types, Practices Projects. Bookings occur
