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]
