Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    33

    Unanswered: SQL Error with OLEDB connection

    My database has 3 tables, each has a varying number of records.

    I am trying to query T1 to get all records then outer join all records in T2 and T3, but I keep getting errors.

    SELECT * FROM (Table1 T1 FULL OUTER JOIN Table2 T2 ON T1.ID = T2.ID) FULL OUTER JOIN Table3 T3 ON T1.ID = T3.ID

    This appears as though it should return all the records, but it fails miserably.

    If this isn't the correct way to do this, how can I return all records in Table1 and all associated data in Table2 and Table3

    and all the tables are one to one relation.

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    AFAIK access cannot do FULL OUTER JOIN (unless you use passthrough queries to a data engine that does understand full outer joins).


    here's the two table Left Union Right workaround extended to three tables.
    it's a rather ugly LeftLeft Union LeftRight Union RightLeft

    Code:
    -- what do we get if full outer is allowed?
    SELECT a.PK AS PK1, a.Txt AS Txt1, b.PK AS PK2, b.Txt AS Txt2, c.PK AS PK3, c.Txt AS Txt3
    FROM dbo.TB1 a
    FULL OUTER JOIN dbo.TB2 b
    ON a.PK = b.PK
    FULL OUTER JOIN dbo.TB3 c
    ON a.PK = c.PK;
    
    --results:
    --PK1         Txt1       PK2         Txt2       PK3         Txt3
    ------------- ---------- ----------- ---------- ----------- ----------
    --1           one        NULL        NULL       1           one
    --2           two        2           two        NULL        NULL
    --4           three      4           four       4           four
    --NULL        NULL       3           three      NULL        NULL
    --NULL        NULL       NULL        NULL       5           five
    
    
    -- what do we get from LeftLeft Union LeftRight Union RightLeft?
    SELECT a.PK as PK1, a.Txt AS Txt1, b.PK AS PK2, b.Txt AS Txt2, c.PK AS PK3, c.Txt AS Txt3
    FROM dbo.TB1 a
    LEFT JOIN dbo.TB2 b
    ON a.PK = b.PK
    LEFT JOIN dbo.TB3 c
    ON a.PK = c.PK
    UNION
    SELECT a.PK as PK1, a.Txt AS Txt1, b.PK AS PK2, b.Txt AS Txt2, c.PK AS PK3, c.Txt AS Txt3
    FROM dbo.TB1 a
    RIGHT JOIN dbo.TB2 b
    ON a.PK = b.PK
    LEFT JOIN dbo.TB3 c
    ON a.PK = c.PK
    UNION
    SELECT a.PK as PK1, a.Txt AS Txt1, b.PK AS PK2, b.Txt AS Txt2, c.PK AS PK3, c.Txt AS Txt3
    FROM dbo.TB1 a
    LEFT JOIN dbo.TB2 b
    ON a.PK = b.PK
    RIGHT JOIN dbo.TB3 c
    ON a.PK = c.PK
    
    --results:
    --PK1         Txt1       PK2         Txt2      PK3        Txt3
    ------------- ---------- ----------- ---------- ----------- ----------
    --NULL        NULL       NULL        NULL       5           five
    --NULL        NULL       3           three      NULL        NULL
    --1           one        NULL        NULL       1           one
    --2           two        2           two        NULL        NULL
    --4           three      4           four       4           four
    at first sight it seems to get the same result
    but it would take forever to read through a result set with more than just the few rows i used here.

    izy

    ------------- edit

    doh!

    the LeftLeft in the LeftLeft Union LeftRight Union RightLeft contributes nothing!
    Code:
    -- LeftRight Union RightLeft
    SELECT a.PK as PK1, a.Txt AS Txt1, b.PK AS PK2, b.Txt AS Txt2, c.PK AS PK3, c.Txt AS Txt3
    FROM dbo.TB1 a
    RIGHT JOIN dbo.TB2 b
    ON a.PK = b.PK
    LEFT JOIN dbo.TB3 c
    ON a.PK = c.PK
    UNION
    SELECT a.PK as PK1, a.Txt AS Txt1, b.PK AS PK2, b.Txt AS Txt2, c.PK AS PK3, c.Txt AS Txt3
    FROM dbo.TB1 a
    LEFT JOIN dbo.TB2 b
    ON a.PK = b.PK
    RIGHT JOIN dbo.TB3 c
    ON a.PK = c.PK;
    
    -- results:
    --PK1         Txt1       PK2         Txt2       PK3         Txt3
    ------------- ---------- ----------- ---------- ----------- ----------
    --NULL        NULL       NULL        NULL       5           five
    --NULL        NULL       3           three      NULL        NULL
    --1           one        NULL        NULL       1           one
    --2           two        2           two        NULL        NULL
    --4           three      4           four       4           four
    Last edited by izyrider; 02-20-11 at 15:04.
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    or do you really mean "how can I return all records in Table1 and all associated data in Table2 and Table3"?
    that would just be a couple of left joins.

    using the same test data as above:
    Code:
    SELECT a.PK as PK1, a.Txt AS Txt1, b.PK AS PK2, b.Txt AS Txt2, c.PK AS PK3, c.Txt AS Txt3
    FROM dbo.TB1 a
    LEFT JOIN dbo.TB2 b
    ON a.PK = b.PK
    LEFT JOIN dbo.TB3 c
    ON a.PK = c.PK;
    
    -- results:
    --PK1         Txt1       PK2         Txt2       PK3         Txt3
    ------------- ---------- ----------- ---------- ----------- ----------
    --1           one        NULL        NULL       1           one
    --2           two        2           two        NULL        NULL
    --4           three      4           four       4           four
    and, er, my typing skills ran away with me TB1.Txt for TB1.PK=4 should certainly read "four" and not the "three" in the results (here and previous post).

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Feb 2009
    Posts
    33
    I have a database provided to me from a client. It is crap ... 247 fields and its no use trying to make sense of it in any kind of grid because it is just too much ...

    So I have split the one table into three different tables with the idea that I can query the important stuff (table1) and show it in a grid and the ancillary stuff (tables 2 and 3) I can simply join and display the fields as needed in various textbox controls in my form.

    Since table3 doesn't have matching records (the client asked me to add more fields) I figured a full outer join would return the fields as null and everything would be good.

    Anyway, I managed to get the results I needed with this:

    Code:
    SELECT * FROM ((Table1 LEFT OUTER JOIN Table3 ON Table1.ID = Table3.ID) LEFT OUTER JOIN Table2 ON Table1.ID = Table2.ID)
    I still have the problem of updating the records though. Is it even possible to update a record joined like this?

Posting Permissions

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