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

Solution 1:

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.

Solution 2:

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)

Solution 3:

Try, the following:

SELECT 
  a.number, a.name, b.ref, c.ref, c.firmref, d.name 
FROM 
 jobs a, teams b 
     LEFTOUTERJOIN teamfirms c ON b.ref = c.team
        LEFTOUTERJOIN 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.

Solution 4:

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)
 LEFTOUTERJOIN teamfirms c ON (b.ref = c.team)
 LEFTOUTERJOIN 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.

Solution 5:

Try the following:

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