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 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 | | | > 0000-00-00 00:00:00 | | > | Booking_End_Date | datetime | | | > 0000-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 > | > +--------------------+--------------+------+-----+---------+----------------+ > | 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 | > | > +--------------------+--------------+------+-----+---------+----------------+ > 12 rows in set (0.01 sec) > > mysql> desc Work_Types; > +--------------+-------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +--------------+-------------+------+-----+---------+----------------+ > | 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) | | | | | > +--------------+-------------+------+-----+---------+----------------+ > 4 rows in set (0.01 sec) > > mysql> > >
Thank you for posting your table structures. It makes your questions MUCH easier to answer. However, as you can see in my response, the DESC command usually creates output that is wrapped by many email clients. I recommend, for next time, trying the \G option of SHOW CREATE TABLE. It lines up everything vertically and it provides not just the field definitions but your index and foreign key definitions as well (those can be VERY helpful in certain cases). Its output is also ready-to-use if anyone wants to recreate your table structure while helping you. Here is a sample of what one of those commands would look like (notice I replaced the semicolon with \G):
SHOW CREATE TABLE Work_Types\G
To answer your question: I think this will help you to identify which Practices have Projects where none of the Project's work_types =1:
SELECT p.Practice_ID ,p.Practice_Name ,b.Booking_ID ,wt.Project_ID ,SUM(If(wt.Work_Type=1,1,0)) as WorkType_1 ,count(1) as TotalWorkType FROM Practice p INNER JOIN Booking 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;
This same query will also help you to identify Projects where there are more than one Work_Type=1's. All you need to do is change the HAVING clause like this
SELECT p.Practice_ID ,p.Practice_Name ,b.Booking_ID ,wt.Project_ID ,SUM(If(wt.Work_Type=1,1,0)) as WorkType_1 ,count(1) as TotalWorkType FROM Practice p INNER JOIN Booking 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>1;
If you are wondering about the clause "GROUP BY 1,2,3,4", that is a MySQL shortcut to say that I want to GROUP on those columns. In other RDBMS systems I would have needed to type
GROUP BY p.Practice_ID,p.Practice_Name,b.Booking_ID,wt.Project_ID
I like the shortcut; I think it's just easier to read.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
