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