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