List:General Discussion« Previous MessageNext Message »
From:Jay Blanchard Date:May 4 2005 11:36am
Subject:RE: How can I find this data?
View as plain text  
[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
Thread
How can I find this data?shaun thornburgh4 May
  • Re: How can I find this data?SGreen4 May
    • Re: How can I find this data?shaun thornburgh5 May
RE: How can I find this data?Jay Blanchard4 May
  • RE: How can I find this data?shaun thornburgh4 May