×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Multiple Left Joins

Multiple Left Joins

Multiple Left Joins

(OP)
I have been repeatedly failing to do something that seems like it should work.

I'm trying to retrieve records from my PROJECTS table based on a WHERE but I am also usong some joins to add useful columns to the resultset.

I have a table PROJECTS, that includes
  a "Ref" field
  a "Name" field
  a "CustomerRef" field that refers to a contact
I have a table CONTACTS, that includes
  a "Ref" field
  a "Name"
I have a table ROLES that includes three fields
   "ProjectRef" that refers to a record in PROJECTS
   "Role"  (name of a role, such as 'ProjectManager')     
   "ContactRef" that refers to a record in CONTACTS
This third table allows a many to many relationship between people and projects.  

So I want to list projects along with the  name of the customer and the  name of the ProjectManager for each project.

SELECT  
 PROJ.Ref  AS "Proj Ref",
 PROJ.Name AS "Proj Name",
 CUST.Name     AS "Cust name",
 PM.Name  AS "Proj Manr"
FROM
 PROJECTS AS PROJ
 LEFT JOIN CONTACTS AS CUST ON PROJ.CustomerRef = CUST.Ref
 LEFT JOIN ROLES ON (ROLES.ProjectRef = PROJ.Ref) AND
   (ROLES.Role = 'ProjectManager')
 LEFT JOIN CONTACTS AS PM   ON ROLES.ContactRef = PM.Ref
WHERE
 etc etc

I can retrieve all the records I want without the last LEFT join but I need that to display the Name (rather than the Ref) of the person who is the ProjectManager on each project.

When I leave out the PM.Name column and the last LEFT JOIN, I get all the records I expect to get.
When I add the last LEFT JOIN, I get no records at all.

Am I trying to do something LEFT JOIN cannot do ?
Is there a better way ?

Thanks in advance.

RE: Multiple Left Joins

Quote:

Am I trying to do something LEFT JOIN cannot do ?
from a syntactic viewpoint, no, not at all

if you aren't getting any data at all when you add the last LEFT JOIN, i'll bet it's because of your WHERE conditions

we can only diagnose your problems further if you show us some actual sample data, and maybe the entire query  

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: Multiple Left Joins

(OP)
R937,

I simplified things somewhat to post this.
There are a whole load of other columns & stuff left out for clarity.

But regarding the WHERE clause, it is the same for both my scenarios and relates to items in the original PROJECTS table, i.e. is completely  independent of the extra tables referred to in the JOINS .  Yet when I add that last LEFT JOIN, it returns nothing.

I don't see yet how adding the last LEFT JOIN affects which records are selected. I feel I must be missing something obvious.


Another potentially useful bit of information.
Without the last LEFT JOIN, the search takes a couple of seconds (understandable as I am searching across PROJECTS and CONTACTS which both have perhaps 1000 records . When I add the last LEFT JOIN, it returns pretty much instantly with no records.  But no SQL syntax error.

And one more.  I am using Derby (aka Cloudscape aka Java DB) but I have not been able to find any docs referring to non standard LEFT JOIN behaviour.

RE: Multiple Left Joins

(OP)
R937

It turns out that
- I was wrong, there WAS a SQL exception (but my app was sending it elsewhere where I was not looking at it)
- I probably need to get glasses - I had a "." where I should have had a "," inmy list of columns to display, and whenever I added the PM.Name column I was in effect insertign a syntax error.

Amazing what you find in the morning that you could not for the life of you find the night before at midnight when you're frazzled.

Thanks again for your post.  It gave me the resolve to plough on rather than abandon the approach.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close