Multiple Left Joins

[griffard]

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.

 

[r937]

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

 

[griffard]

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.

 

[griffard]

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.