List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 23 2004 3:14pm
Subject:Re: Help with a join query please!
View as plain text  
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
> 

Thread
Help with a join query please!shaun thornburgh20 Dec
  • Re: Help with a join query please!Sasha Pachev21 Dec
    • Re: Help with a join query please!shaun thornburgh23 Dec
      • Re: Help with a join query please!SGreen23 Dec
        • Re: Help with a join query please!shaun thornburgh29 Dec
          • Re: Help with a join query please!SGreen29 Dec
            • Re: Help with a join query please!shaun thornburgh29 Dec