"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]
> > >
>
>
