SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ambiguous outer join

    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;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
             -- 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •