Hi,
I have (among others) three tables in my database that i am struggling with a join query on. The database holds dates for Bookings. If Users are Allocated to a particular Project they can be booked. However if a user is booked but then unallocated I want to be able to display all peolple allocated to that project plus the person originally booked. Here are my efforts so far:
SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname;
I am using version 3.23.54. The query works but doesnt return the required values. The query returns all of the users allocated to the project, not the user who is currently booked but not allocated to the project. Here are the table definitions:
mysql> DESCRIBE Allocations; +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | Project_ID | int(11) | | PRI | 0 | | | User_ID | int(11) | | PRI | 0 | | +------------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> DESCRIBE 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 | |
| PCT_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 | |
| Work_Type_ID | int(2) | YES | | NULL | |
| Additional_Notes | text | YES | | NULL | |
| Form_Recieved | char(3) | | | | |
| Section_C | char(3) | | | | |
| Date_Difference | varchar(20) | | | n/a | |
+-------------------------+-------------+------+-----+---------------------+----------------+
20 rows in set (0.00 sec)
mysql> DESCRIBE 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 | | | User_ID | int(11) | YES | | NULL | | +--------------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
mysql> DESCRIBE Users;
+----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+----------------+
| User_ID | int(11) | | PRI | NULL | auto_increment |
| Client_ID | int(3) | YES | | NULL | |
| User_Username | varchar(40) | | | | |
| User_Firstname | varchar(50) | YES | | NULL | |
| User_Lastname | varchar(50) | YES | | NULL | |
| User_Password | varchar(20) | YES | | NULL | |
| User_Type | varchar(20) | | | Nurse | |
| User_Email | varchar(100) | YES | | NULL | |
| User_Manager_Email | varchar(100) | YES | | NULL | |
| User_Manager_Email_2 | varchar(100) | YES | | NULL | |
| User_Manager_Email_3 | varchar(100) | YES | | NULL | |
| User_Manager_Email_4 | varchar(100) | YES | | NULL | |
| User_Manager_Email_5 | varchar(100) | YES | | NULL | |
| User_Manager_Email_6 | varchar(100) | YES | | NULL | |
| User_Location | varchar(40) | YES | | NULL | |
| Mobile_Number | varchar(20) | YES | | NULL | |
| Rep_Nurse_1 | int(11) | YES | | NULL | |
| Rep_Nurse_2 | int(11) | YES | | NULL | |
| Booking_Credits | int(11) | YES | | NULL | |
+----------------------+--------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)
Thanks for your help here,
Shaun
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
