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';
And if i put a join in the query i get hundreds of results:
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';
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)
_________________________________________________________________ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
