"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
