Re: Help with a join query please

Thu, 16 Dec 2004 15:29:32 -0800



From: "Rhino" <[EMAIL PROTECTED]>
To: "shaun thornburgh" <[EMAIL PROTECTED]>,<[EMAIL PROTECTED]>
Subject: Re: Help with a join query please
Date: Thu, 16 Dec 2004 14:59:48 -0500


----- Original Message -----
From: "shaun thornburgh" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, December 16, 2004 2:02 PM
Subject: Help with a join query please


> 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 hope this makes sense!
>
We could do a lot more for you if you mentioned a few things:
a) which version of MySQL you are using
b) whether the query works or not the way it stands
c) if it does work, what is wrong with the answer you are getting
d) if it doesn't work, what error message you are getting

And if you REALLY want to help us help you, you could provide definitions of
the tables and a few rows of typical data from each table to help us
visualize the problem better.

Rhino


Sorry guys!

a) I am using version 3.23.54
b) The query works but doesnt return the required values
c) The query returns all of the users allocated to the project, not the user who is currently booked but not allocated to the project
d) No error message!

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]

Reply via email to