Re: Help with a join query
Yoed Anis wrote: Hi all, I'm trying to do the following. I have three table: Table a has address information: address_id | City | State | Zip 1Austin TX 78758 2 Dallas TX 77000 3 Galveston TX 77550 Table b has information about the location: address_id | Location_id | Location_name 11The Place 12The Place Before 23A shop Table c has montlhy sales history Locationid | MonthYear | Sales 12005-01-01 299 12005-02-01100 12005-10-01300 22005-01-01 154 32005-10-1099 Not every location has sales information. I am trying to create a query where I can SELECT the Locationname, City, State, Zip, and the SUM(sales) if the place has sales. So far, despite playing around with joins for more hours than one should ever dedicated to the matter, I haven't been able to include SUM(sales) without excluding listings without sales. So far this is my best shot: SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a, b LEFT JOIN c ON (b.locationid = c.locationid) WHERE a.address_id = b.address_id AND monthyear 2005-01-01 GROUP BY c.locationid SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a INNER JOIN b ON a.address_id = b.address_id LEFT JOIN c ON b.locationid = c.locationid and monthyear2005-01-10 GROUP BY c.locationid This however, will return only records with Sales and not those without it. I haven't been able to force adding empty rows from table c... Doing AND c.locationid IS NULL returns no results at all. Any help would GREATLY be appreciated!!! Thank you!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a join query
Hi all, I'm trying to do the following. I have three table: Table a has address information: address_id | City | State | Zip 1Austin TX 78758 2 Dallas TX 77000 3 Galveston TX 77550 Table b has information about the location: address_id | Location_id | Location_name 11The Place 12The Place Before 23A shop Table c has montlhy sales history Locationid | MonthYear | Sales 12005-01-01 299 12005-02-01100 12005-10-01300 22005-01-01 154 32005-10-1099 Not every location has sales information. I am trying to create a query where I can SELECT the Locationname, City, State, Zip, and the SUM(sales) if the place has sales. So far, despite playing around with joins for more hours than one should ever dedicated to the matter, I haven't been able to include SUM(sales) without excluding listings without sales. So far this is my best shot: SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a, b LEFT JOIN c ON (b.locationid = c.locationid) WHERE a.address_id = b.address_id AND monthyear 2005-01-01 GROUP BY c.locationid This however, will return only records with Sales and not those without it. I haven't been able to force adding empty rows from table c... Doing AND c.locationid IS NULL returns no results at all. Any help would GREATLY be appreciated!!! Thank you!!
Help with a JOIN query please
Hi, I have ( among others ) three tables in my database: Claims, Expenses and Mileage. A claim can contain many expense entries and many mileage entries. I am using the follwing query to show the total expenses and mileage per claim for a particulare user: SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage FROM Claims C LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID WHERE C.Claimant_ID = '1' GROUP BY C.Claim_ID The problem is if there are two mileage entries and one expense entry the expense total is doubled (and vice versa), can some explain this to me please? Thanks for your help. TABLE DEFINITIONS: mysql desc Claims; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Claim_ID| int(11) | | PRI | NULL| auto_increment | | Claimant_ID | int(11) | | | 0 || | Description | varchar(50) | | | || | Status | varchar(50) | YES | | Open|| | Submission_Date | datetime| YES | | NULL|| | Approval_Date | datetime| YES | | NULL|| | Approver_ID | int(11) | YES | | NULL|| +-+-+--+-+-++ 7 rows in set (0.00 sec) mysql desc Expenses; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | Expense_ID | int(11) | | PRI | NULL| auto_increment | | Claim_ID| int(11) | YES | | NULL| | | Description | varchar(50) | YES | | NULL| | | Expense_Category_ID | int(11) | YES | | NULL| | | Insertion_Date | date | YES | | NULL| | | Project_ID | int(11) | YES | | NULL| | | Amount | decimal(10,2) | YES | | NULL| | | Rate_ID | int(11) | YES | | NULL| | | Supplier_ID | int(11) | YES | | NULL| | | Receipt | varchar(10) | YES | | NULL| | | Receipt_Date| varchar(10) | YES | | NULL| | | VAT_Receipt | varchar(10) | YES | | NULL| | | VAT_Amount | decimal(10,2) | YES | | NULL| | +-+---+--+-+-++ 13 rows in set (0.00 sec) mysql desc Mileage; ++--+--+-+++ | Field | Type | Null | Key | Default| Extra | ++--+--+-+++ | Mileage_ID | int(11) | | PRI | NULL | auto_increment | | Claim_ID | int(11) | | | 0 || | Project_ID | int(11) | | | 0 || | Insertion_Date | date | | | -00-00 || | Description| varchar(255) | | ||| | Start_Mileage | int(11) | | | 0 || | End_Mileage| int(11) | | | 0 || | Mileage| int(11) | | | 0 || ++--+--+-+++ 8 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a JOIN query please
Hello shaun, May be with subselects you can do what you want: SELECT C.*,(select SUM(E.Amount) from Expenses E where E.Claim_ID = C.Claim_ID)AS Amount, (select SUM(M.Mileage) from Mileage M where M.Claim_ID = C.Claim_ID) AS Mileage FROM Claims C WHERE C.Claimant_ID = '1' st Hi, st I have ( among others ) three tables in my database: Claims, Expenses and st Mileage. A claim can contain many expense entries and many mileage entries. st I am using the follwing query to show the total expenses and mileage per st claim for a particulare user: st SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage st FROM Claims C st LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID st LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID st WHERE C.Claimant_ID = '1' st GROUP BY C.Claim_ID st The problem is if there are two mileage entries and one expense entry the st expense total is doubled (and vice versa), can some explain this to me st please? st Thanks for your help. st TABLE DEFINITIONS: mysql desc Claims; st +-+-+--+-+-++ st | Field | Type| Null | Key | Default | Extra | st +-+-+--+-+-++ st | Claim_ID| int(11) | | PRI | NULL| auto_increment | st | Claimant_ID | int(11) | | | 0 | st | Description | varchar(50) | | | | st | Status | varchar(50) | YES | | Open| st | Submission_Date | datetime| YES | | NULL| st | Approval_Date | datetime| YES | | NULL| st | Approver_ID | int(11) | YES | | NULL| st +-+-+--+-+-++ st 7 rows in set (0.00 sec) mysql desc Expenses; st +-+---+--+-+-++ st | Field | Type | Null | Key | Default | Extra st | st +-+---+--+-+-++ st | Expense_ID | int(11) | | PRI | NULL| st auto_increment | st | Claim_ID| int(11) | YES | | NULL| st | st | Description | varchar(50) | YES | | NULL| st | st | Expense_Category_ID | int(11) | YES | | NULL| st | st | Insertion_Date | date | YES | | NULL| st | st | Project_ID | int(11) | YES | | NULL| st | st | Amount | decimal(10,2) | YES | | NULL| st | st | Rate_ID | int(11) | YES | | NULL| st | st | Supplier_ID | int(11) | YES | | NULL| st | st | Receipt | varchar(10) | YES | | NULL| st | st | Receipt_Date| varchar(10) | YES | | NULL| st | st | VAT_Receipt | varchar(10) | YES | | NULL| st | st | VAT_Amount | decimal(10,2) | YES | | NULL| st | st +-+---+--+-+-++ st 13 rows in set (0.00 sec) mysql desc Mileage; st ++--+--+-+++ st | Field | Type | Null | Key | Default| Extra | st ++--+--+-+++ st | Mileage_ID | int(11) | | PRI | NULL | auto_increment | st | Claim_ID | int(11) | | | 0 | st | Project_ID | int(11) | | | 0 | st | Insertion_Date | date | | | -00-00 | st | Description| varchar(255) | | || st | Start_Mileage | int(11) | | | 0 | st | End_Mileage| int(11) | | | 0 | st | Mileage| int(11) | | | 0 | st ++--+--+-+++ st 8 rows in set (0.00 sec) mysql -- Best regards, Krasimir_Slaveykov, 18 Ìàðò 2005 ã., 12:54:56 mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** **** *** ///| |// *** *** *** *** *** *** ** ****** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a JOIN query please
shaun thornburgh [EMAIL PROTECTED] wrote on 03/17/2005 06:46:22 PM: Hi, I have ( among others ) three tables in my database: Claims, Expenses and Mileage. A claim can contain many expense entries and many mileage entries. I am using the follwing query to show the total expenses and mileage per claim for a particulare user: SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage FROM Claims C LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID WHERE C.Claimant_ID = '1' GROUP BY C.Claim_ID The problem is if there are two mileage entries and one expense entry the expense total is doubled (and vice versa), can some explain this to me please? Thanks for your help. TABLE DEFINITIONS: mysql desc Claims; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Claim_ID| int(11) | | PRI | NULL| auto_increment | | Claimant_ID | int(11) | | | 0 | | | Description | varchar(50) | | | | | | Status | varchar(50) | YES | | Open| | | Submission_Date | datetime| YES | | NULL| | | Approval_Date | datetime| YES | | NULL| | | Approver_ID | int(11) | YES | | NULL| | +-+-+--+-+-++ 7 rows in set (0.00 sec) mysql desc Expenses; +-+---+--+-+- ++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+- ++ | Expense_ID | int(11) | | PRI | NULL| auto_increment | | Claim_ID| int(11) | YES | | NULL| | | Description | varchar(50) | YES | | NULL| | | Expense_Category_ID | int(11) | YES | | NULL| | | Insertion_Date | date | YES | | NULL| | | Project_ID | int(11) | YES | | NULL| | | Amount | decimal(10,2) | YES | | NULL| | | Rate_ID | int(11) | YES | | NULL| | | Supplier_ID | int(11) | YES | | NULL| | | Receipt | varchar(10) | YES | | NULL| | | Receipt_Date| varchar(10) | YES | | NULL| | | VAT_Receipt | varchar(10) | YES | | NULL| | | VAT_Amount | decimal(10,2) | YES | | NULL| | +-+---+--+-+- ++ 13 rows in set (0.00 sec) mysql desc Mileage; ++--+--+-+++ | Field | Type | Null | Key | Default| Extra | ++--+--+-+++ | Mileage_ID | int(11) | | PRI | NULL | auto_increment | | Claim_ID | int(11) | | | 0 | | | Project_ID | int(11) | | | 0 | | | Insertion_Date | date | | | -00-00 | | | Description| varchar(255) | | || | | Start_Mileage | int(11) | | | 0 | | | End_Mileage| int(11) | | | 0 | | | Mileage| int(11) | | | 0 | | ++--+--+-+++ 8 rows in set (0.00 sec) mysql It's happening because you are joining two child tables at once. Maybe a sequence of diagrams will help. This is what happens within the DB engine whenever you make a JOIN. Since you were nice enough to post your table structures (THANK YOU!!!) I will use your tables and problem query as examples. Something simple: SELECT ... FROM Claims C LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID; Step one of any query is to identify all of your source data. In this case, we are combining the records of two tables so that it behaves like a single larger table. This internally created, virtual table represents all possible combinations of rows that satisfy the ON conditions of your JOIN clauses. INTERNAL VIRTUAL TABLE A +---+-+ | all columns of Claims | all columns of Expenses | +---+-+ | Claims row 1 | all null values | +---+-+ | Claims row 2 | Expenses row 246| +---+-+ | Claims row 2 | Expenses row 248| +---+-+ | Claims row 2 |
Re: Help with a join query please!
Hi Shawn, Thanks for your reply, but your query seems to return everyone allocated to that project plus everyone who has ever been booked on that project. What I need is everyone who is currently allocated to that project and the staff member who has been booked for the booking in question whether they have been allocated to the project or not. In reply to your quesion bookings don't relate to allocations directly. A booking relates to a project by Project_ID and to a user by User_ID. Allocations contains both of these fields to determine who is 'Allocated' to a particular project. Thanks again for your help! From: [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: Re: Help with a join query please! Date: Thu, 23 Dec 2004 10:14:24 -0500 So -- what's the field that relates a booking to an allocation? Do they share a project_ID or what? If they do, you might try this: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = '11' OR B.Project_ID = '11' ORDER BY User_Firstname; soapbox A pet peeve of mine is when people 'quote' NUMBERS. According to the extremely well written manual, you only need to quote STRING values and DATETIME values. Unless the columns Project_ID and Booking_ID are some form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to quote their values in queries. It forces the query engine to perform an unnecessary internal type conversion. Here is what I think your query should look like: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = 11 OR B.Project_ID = 11 ORDER BY User_Firstname; /soapbox I used SELECT DISTINCT so that in the event that someone was both BOOKED and ALLOCATED to the same project, you only got them listed once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine shaun thornburgh [EMAIL PROTECTED] wrote on 12/23/2004 08:37:37 AM: Hi, Thanks for your reply but that produces exactly the same result... Any ideas? From: Sasha Pachev [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Help with a join query please! Date: Tue, 21 Dec 2004 14:57:43 -0700 shaun thornburgh wrote: 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; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please!
shaun thornburgh [EMAIL PROTECTED] wrote on 12/29/2004 03:00:08 PM: Hi Shawn, Thanks for your reply, but your query seems to return everyone allocated to that project plus everyone who has ever been booked on that project. What I need is everyone who is currently allocated to that project and the staff member who has been booked for the booking in question whether they have been allocated to the project or not. How can I determine the booking in question? What confluence of values makes a generic booking the booking in question. If you could walk me through the process of finding it manually (compare field x on table y to field z on table bb then go over to table c and look at ...) I can get you the data you need. In reply to your quesion bookings don't relate to allocations directly. A booking relates to a project by Project_ID and to a user by User_ID. Allocations contains both of these fields to determine who is 'Allocated' to a particular project. Thanks again for your help! any time :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine From: [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: Re: Help with a join query please! Date: Thu, 23 Dec 2004 10:14:24 -0500 So -- what's the field that relates a booking to an allocation? Do they share a project_ID or what? If they do, you might try this: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = '11' OR B.Project_ID = '11' ORDER BY User_Firstname; soapbox A pet peeve of mine is when people 'quote' NUMBERS. According to the extremely well written manual, you only need to quote STRING values and DATETIME values. Unless the columns Project_ID and Booking_ID are some form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to quote their values in queries. It forces the query engine to perform an unnecessary internal type conversion. Here is what I think your query should look like: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = 11 OR B.Project_ID = 11 ORDER BY User_Firstname; /soapbox I used SELECT DISTINCT so that in the event that someone was both BOOKED and ALLOCATED to the same project, you only got them listed once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine shaun thornburgh [EMAIL PROTECTED] wrote on 12/23/2004 08:37:37 AM: Hi, Thanks for your reply but that produces exactly the same result... Any ideas? From: Sasha Pachev [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Help with a join query please! Date: Tue, 21 Dec 2004 14:57:43 -0700 shaun thornburgh wrote: 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; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please!
Hi Shawn, Sorry for the confusions! The booking in question is where B.Booking_ID = x as in the original query: 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; Thanks, Shaun From: [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: Re: Help with a join query please! Date: Wed, 29 Dec 2004 15:58:43 -0500 shaun thornburgh [EMAIL PROTECTED] wrote on 12/29/2004 03:00:08 PM: Hi Shawn, Thanks for your reply, but your query seems to return everyone allocated to that project plus everyone who has ever been booked on that project. What I need is everyone who is currently allocated to that project and the staff member who has been booked for the booking in question whether they have been allocated to the project or not. How can I determine the booking in question? What confluence of values makes a generic booking the booking in question. If you could walk me through the process of finding it manually (compare field x on table y to field z on table bb then go over to table c and look at ...) I can get you the data you need. In reply to your quesion bookings don't relate to allocations directly. A booking relates to a project by Project_ID and to a user by User_ID. Allocations contains both of these fields to determine who is 'Allocated' to a particular project. Thanks again for your help! any time :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine From: [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: Re: Help with a join query please! Date: Thu, 23 Dec 2004 10:14:24 -0500 So -- what's the field that relates a booking to an allocation? Do they share a project_ID or what? If they do, you might try this: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = '11' OR B.Project_ID = '11' ORDER BY User_Firstname; soapbox A pet peeve of mine is when people 'quote' NUMBERS. According to the extremely well written manual, you only need to quote STRING values and DATETIME values. Unless the columns Project_ID and Booking_ID are some form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to quote their values in queries. It forces the query engine to perform an unnecessary internal type conversion. Here is what I think your query should look like: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = 11 OR B.Project_ID = 11 ORDER BY User_Firstname; /soapbox I used SELECT DISTINCT so that in the event that someone was both BOOKED and ALLOCATED to the same project, you only got them listed once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine shaun thornburgh [EMAIL PROTECTED] wrote on 12/23/2004 08:37:37 AM: Hi, Thanks for your reply but that produces exactly the same result... Any ideas? From: Sasha Pachev [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Help with a join query please! Date: Tue, 21 Dec 2004 14:57:43 -0700 shaun thornburgh wrote: 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; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please!
Hi, Thanks for your reply but that produces exactly the same result... Any ideas? From: Sasha Pachev [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Help with a join query please! Date: Tue, 21 Dec 2004 14:57:43 -0700 shaun thornburgh wrote: 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; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please!
So -- what's the field that relates a booking to an allocation? Do they share a project_ID or what? If they do, you might try this: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = '11' OR B.Project_ID = '11' ORDER BY User_Firstname; soapbox A pet peeve of mine is when people 'quote' NUMBERS. According to the extremely well written manual, you only need to quote STRING values and DATETIME values. Unless the columns Project_ID and Booking_ID are some form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to quote their values in queries. It forces the query engine to perform an unnecessary internal type conversion. Here is what I think your query should look like: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = 11 OR B.Project_ID = 11 ORDER BY User_Firstname; /soapbox I used SELECT DISTINCT so that in the event that someone was both BOOKED and ALLOCATED to the same project, you only got them listed once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine shaun thornburgh [EMAIL PROTECTED] wrote on 12/23/2004 08:37:37 AM: Hi, Thanks for your reply but that produces exactly the same result... Any ideas? From: Sasha Pachev [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Help with a join query please! Date: Tue, 21 Dec 2004 14:57:43 -0700 shaun thornburgh wrote: 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; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please!
shaun thornburgh wrote: 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; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 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| | | -00-00 00:00:00 | | | Booking_End_Date| datetime| | | -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 |
Re: Help with a join query please
- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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! Shaun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please
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| | | -00-00 00:00:00 | | | Booking_End_Date| datetime| | | -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
help with SQL (join?) query
I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? Thanks, Kris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- Peter Valdemar Mørch http://www.morch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
How so? Is there something you didn't understand? Peter's solution is the right idea. You need to join the groups table to the users table once to get the creatorname and again to get the ownername. Maybe it will be clearer if we rewrite the query to make the join conditions explicit: SELECT g.id, g.name, o.name AS 'owner', c.name AS 'creator' FROM groups g JOIN users o ON groups.owner = o.uid JOIN users c ON groups.creator = c.uid; Does that help? Kris wrote: No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
I think he gave you the right answer. you can also use joins instead of where, but it is the same idea, not sure which one of the two will be faster though: select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups left join users as owner on owner.uid = groups.groupowner left join users as creator on groups.groupcreator = creator.uid - Original Message - From: Kris [EMAIL PROTECTED] To: Peter Valdemar Mørch [EMAIL PROTECTED] Cc: Kris zoob-at-doomstar.com |Lists| [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 7:08 PM Subject: Re: help with SQL (join?) query No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query
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]
Help with a join query
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]
