ambiguous outer join

[johnjohn2]

I'm testing a query out in Access2K that has all it's tables connected to postgresql 7.4. Once my test queries run fine I put them in my php code. I'm testing the following query out but Access complains that there is an ambiguous outer join. It's only when I include the tbl71 connected to tbl63 that it complains. If I create a seperate query for tbl63 and tbl71 and then use that query in the sql statement it works fine. So I'm thinking there must be a way around this to get it working without the 2 seperate queries. Can anyone shed some light on this one?


SELECT
tbl95view1.col27,
tbl95view1.col3,
tbl95view1.col28,
tbl95view1.col35,
tbl88view2.col1,
tbl45.col5,
tbl47.col2,
tbl47.col3,
tbl47.col17,
tbl45.col4,
tbl91view1.col19,
tbl63.col3,
tbl53.col2,
tbl91view1.col21,
tbl91view1.col22,
tbl91view1.col21*tbl91view1.col22 AS totalpay

FROM
(((((tbl95view1 INNER JOIN tbl88view2 ON tbl95view1.col35 = tbl88view2.col20)
INNER JOIN tbl91view1 ON tbl88view2.col1 = tbl91view1.col13)
INNER JOIN (tbl45 INNER JOIN tbl47 ON tbl45.col2 = tbl47.col1) ON tbl88view2.col13 = tbl45.col1)
LEFT JOIN tbl53 ON tbl91view1.col23 = tbl53.col1)
LEFT JOIN tbl63 ON tbl91view1.col27 = tbl63.col1)
INNER JOIN tbl71 ON tbl63.col4 = tbl71.col1

ORDER BY tbl95view1.col35 asc;

[r937]

here's a couple of guidelines for how to approach such a problem

1. write the joins by always joining just one more table to the ones you've already got (going from left to right)

this means don't try to join a pair of tables

you start with tbl95view1 then tbl88view2 then tbl91view1

(aside: if these are the real table names, you need saved queries or views to give them some more meaningful names, and if you've picked those names to preserve confidentiality in your postings here, then you could just as easily call them curly, larry, and moe -- anything would be better than those horrid numbered names, which, i am confident you have already found out yourself, are so susceptible to typos)

but then after the first three, you next try to join a join of tbl45 and tbl47

so, just one at a time, going from left to right

2. if you need to LEFT OUTER to any tables, put them after all the INNER joins

3. if you need to join any table to a table that was joined by LEFT OUTER, then use LEFT OUTER for those as well

so i would change this --

FROM (((((tbl95view1
INNER JOIN tbl88view2 ON tbl95view1.col35 = tbl88view2.col20)
INNER JOIN tbl91view1 ON tbl88view2.col1 = tbl91view1.col13)
INNER JOIN (tbl45 INNER JOIN tbl47 ON tbl45.col2 = tbl47.col1) ON tbl88view2.col13 = tbl45.col1)
LEFT JOIN tbl53 ON tbl91view1.col23 = tbl53.col1)
LEFT JOIN tbl63 ON tbl91view1.col27 = tbl63.col1)
INNER JOIN tbl71 ON tbl63.col4 = tbl71.col1

to this --

FROM ((((((tbl95view1
INNER JOIN tbl88view2 ON tbl95view1.col35 = tbl88view2.col20 )
INNER JOIN tbl91view1 ON tbl88view2.col1 = tbl91view1.col13 )
INNER JOIN tbl45 ON tbl88view2.col13 = tbl45.col1 )
INNER JOIN tbl47 ON tbl45.col2 = tbl47.col1 )
LEFT JOIN tbl53 ON tbl91view1.col23 = tbl53.col1 )
LEFT JOIN tbl63 ON tbl91view1.col27 = tbl63.col1 )
LEFT JOIN tbl71 ON tbl63.col4 = tbl71.col1

here is a "diagram" which illustrates how the tables are joined

         -- 45 -- 47 
        /
95 -- 88 -- 91 -- 53 
              \
               -- 63 -- 71

one worrisome conclusion to draw from this diagram is that if there is any 1-to-many relationship along the paths that diverge from 88 and 91, then you will have a cross join or cartesian effect