shaun thornburgh wrote:
Hi,
I am having trouble with a LEFT JOIN. I have three tables: Users, Projects and Allocations. Users can be allocated to zero or more projects and a project can be allocated to zero or more users. Table descriptions are below.
How can i select a users details and all of the pojects they are allocated to? This query is fine if the user is allocated to one or more projects:
SELECT U.*, P.* FROM Users U, Allocations A, Projects P WHERE A.Project_ID = P.Project_ID AND A.User_ID = U.User_ID AND U.User_ID = '2';
Right. A user assigned to no projects won't have an entry in the Allocations table, so the JOIN condition isn't met. I'm assuming the problem is that the user is missing from the Allocations table, so you need a LEFT JOIN of Users to Allocations.
And if i put a join in the query i get hundreds of results:
Your original query above joins 3 tables. In your next query, you're changing one of the joins from an implicit inner join to a left join, not adding a join.
SELECT U.*, P.* FROM Users U, Allocations A LEFT JOIN Projects P ON P.Project_ID = A.Project_ID AND A.User_ID = U.User_ID WHERE U.User_ID = '2';
First, though it's not the source of the problem, User_ID is an int, so lose the quotes around 2. You're forcing mysql to convert the string '2' to the integer 2.
You have no join condition on the join of Users and Allocations, so you're getting the Cartesian product, hence the hundreds of results. (Yes, I see the "A.User_ID = U.User_ID", but it's part of the ON clause for the LEFT JOIN of A to P, so it's not doing what you meant.)
SELECT U.*, P.* FROM Users U LEFT JOIN Allocations A ON U.User_ID = A.User_ID LEFT JOIN Projects P ON A.Project_ID = P.Project_ID WHERE U.User_ID = 2;
Michael
Any help here would be great, many thanks.
mysql> DESCRIBE Users;
+----------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+----------------+
| User_ID | int(11) | | PRI | NULL | auto_increment |
| User_Username | varchar(40) | | | | |
| User_Password | varchar(20) | YES | | NULL | |
| User_Name | varchar(100) | | | | |
| User_Type | varchar(20) | | | Nurse | |
| User_Email | varchar(100) | YES | | NULL | |
+----------------------+---------------+------+-----+---------+----------------+
17 rows in set (0.01 sec)
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 Projects; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | Project_ID | int(11) | | PRI | NULL | auto_increment | | Project_Name | varchar(100) | | | | | +--------------+--------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
