Multi table joins - can I add an outer join to this?

I'm having a problem moving from a situation where an Outer Join works, to where it fails.

Working (pseudo code example)

SELECT a.number, a.name, b.ref, c.ref, c.firmref
FROM jobs a, teams b LEFT OUTER JOIN teamfirms c ON b.ref = c.team
WHERE a.ref = b.job

There is a many to one relationship between jobs and teams (many teams per job) that is always populated

There may or may not be firms in table c, but the query above gives me the result I would expect (approx 5000 records)

The problem comes when I want to bring in the details about the teams from a fourth table

The code I am trying is below

SELECT a.number, a.na开发者_如何学Pythonme, b.ref, c.ref, c.firmref, d.name
FROM jobs a, teams b LEFT OUTER JOIN teamfirms c ON b.ref = c.team, firms d
WHERE a.ref = b.job
AND d.ref = c.firmref

At this point the NULLS that I am trying to capture disappear and I drop approx 500 records

What am I doing wrong?

take a whack at this.

select a.number, a.name, b.ref, c.ref, c.firmref, d.name
from jobs a left outer join teams b on b.job = a.ref
left outer join teamfirms c on b.ref = c.team
left outer join firms d on c.firmref = d.ref
left outer join table e on a.column = e.column

or you could do

select a.number, a.name, b.ref, c.ref, c.firmref, d.name
from
jobs a, teams b, teamfirms c, firms d
where
a.ref = b.job
and b.ref = c.team
and c.firmref = d.ref

one or the other... not both.

Just to throw this in for good measure...

You use INNER JOIN to return all rows from both tables where there is a match. ie. in the resulting table all the rows and colums will have values.

LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table.

RIGHT OUTER JOIN returns all the rows from the second table, even if there are no matches in the first table.

You are mixing ANSI 89 and 92 JOIN syntax (implicit and explicit JOINs). Try converting the entire query to explicit JOINs. The problem is likely that the new JOIN you're adding (implicit syntax) is INNER and wants to be OUTER, or that you want to resolve the JOINs in a different order (which you can do with parens once you write them all as OUTER JOINs)

Try, the following:

SELECT 
  a.number, a.name, b.ref, c.ref, c.firmref, d.name 
FROM 
 jobs a, teams b 
     LEFT OUTER JOIN teamfirms c ON b.ref = c.team
        LEFT OUTER JOIN firms d on c.firmref = d.ref
WHERE a.ref = b.job 

If it works, you could then try to turn the 2nd LEFT OUTER into an INNER. Possibly incorrectly I've generally left it as an outer when I've needed this sort of thing.

Here is my attempt:

SELECT a.number, a.name, b.ref, c.ref, c.firmref, d.name 
FROM jobs a
 join teams b on (b.job = a.ref)
 LEFT OUTER JOIN teamfirms c ON (b.ref = c.team)
 LEFT OUTER JOIN firms d on (d.ref = c.firmref)

This will join all jobs to team and if a teamfirm exist then also bring firm details. if no team firm relationship you still get your nulls.

Try the following:

SELECT a.number, a.name, b.ref, c.ref, c.firmref, d.name
FROM jobs a, teams b LEFT OUTER JOIN teamfirms c ON b.ref = c.team
LEFT OUTER JOIN firms d ON c.firmref = d.ref
WHERE a.ref = b.job