Re: Help with a join query please! - SGr...@unimin.com - com.mysql.lists.mysql

7 messages in com.mysql.lists.mysqlRe: Help with a join query please!

From	Sent On	Attachments
shaun thornburgh	20 Dec 2004 02:46
Sasha Pachev	21 Dec 2004 13:57
shaun thornburgh	23 Dec 2004 05:37
SGr...@unimin.com	23 Dec 2004 07:14
shaun thornburgh	29 Dec 2004 11:59
SGr...@unimin.com	29 Dec 2004 12:58
shaun thornburgh	29 Dec 2004 13:06

Subject:	Re: Help with a join query please!
From:	SGr...@unimin.com (SGr...@unimin.com)
Date:	12/23/2004 07:14:02 AM
List:	com.mysql.lists.mysql

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" <shau...@hotmail.com> 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 <sas...@surveyz.com>

To: shaun thornburgh <shau...@hotmail.com> CC: mys...@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.