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: SGreen@stripped
>To: "shaun thornburgh" <shaunthornburgh@stripped>
>CC: mysql@stripped,sasha@stripped
>Subject: Re: Help with a join query please!
>Date: Wed, 29 Dec 2004 15:58:43 -0500
>
>"shaun thornburgh" <shaunthornburgh@stripped> 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: SGreen@stripped
> > >To: "shaun thornburgh" <shaunthornburgh@stripped>
> > >CC: mysql@stripped,sasha@stripped
> > >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" <shaunthornburgh@stripped> 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 <sasha@stripped>
> > > > >To: shaun thornburgh <shaunthornburgh@stripped>
> > > > >CC: mysql@stripped
> > > > >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/mysql?unsub=1
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> > > >
> >
> >
