Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: Multiple joins, avoiding cross joined result

    I would like to join three tables, using a common join column in one of the tables without it looking like cross join.

    I am using Access 2003

    So if I have three tables:
    Code:
    CommonTable:
    ID Col1
    -- ----
    1  Item1
    2  Item2
    
    Table1
    ID1 ForeignKey1 Col2
    --- ----------- ----
    1   1           SecondItem1
    2   1           SecondItem2
    
    Table2
    ID2 ForeignKey2 Col3
    --- ----------- ----
    1   1           ThirdItem1
    2   1           ThirdItem2
    So the normal join would be:
    (CommonTable INNER JOIN Table1 ON CommonTable.ID=Table1.ForeignKey) INNER JOIN Table2 ON CommonTable.ID=Table2.ForeignKey

    Which results in:
    Code:
    ID Col1  ID1 ForeignKey1 Col2        ID2 ForeignKey2 Col3
    -- ----- --- ----------- ----------- --- ----------- ----------
    1  Item1 1   1           SecondItem1 1   1           ThirdItem1
    1  Item1 2   1           SecondItem2 1   1           ThirdItem1
    1  Item1 1   1           SecondItem1 2   1           ThirdItem2
    1  Item1 2   1           SecondItem2 2   1           ThirdItem2
    But what I want is to separate the joining of Table1 and Table2 to CommonTable to end up with:
    Code:
    ID Col1  ID1 ForeignKey1 Col2        ID2 ForeignKey2 Col3
    -- ----- --- ----------- ----------- --- ----------- ----------
    1  Item1 1   1           SecondItem1 NULL
    1  Item1 2   1           SecondItem2 NULL
    1  Item1 NULL                        1   1           ThirdItem1
    1  Item1 NULL                        2   1           ThirdItem2
    At first I thought outer joins would do the trick, but gradually realised that didn't help in this case.
    Happy to use intermediate queries or whatever, but I just can't figure this one out

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT CommonTable.ID
         , CommonTable.Col1
         , Table1.ID1
         , Table1.Col2
         , NULL AS ID2
         , NULL AS Col3
      FROM CommonTable 
    INNER 
      JOIN Table1 
        ON Table1.ForeignKey1 = CommonTable.ID
    UNION ALL
    SELECT CommonTable.ID
         , CommonTable.Col1
         , NULL AS ID1
         , NULL AS Col2
         , Table2.ID2
         , Table2.Col3
      FROM CommonTable 
    INNER 
      JOIN Table2 
        ON Table2.ForeignKey2 = CommonTable.ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    Almost perfect!

    Just need to figure out how to convince Access that the NULL column (from the first select) is in fact of a different type.

    Currently it just displays boxes where it should have data. If I chance NULL to 0, it then displays the data correctly.

  4. #4
    Join Date
    Sep 2010
    Posts
    3
    I've done a rough and ready method to fix the column types.

    I've just added:

    SELECT 0 AS ID
    , "" AS Col1
    , 0 AS ID1
    , "" AS Col2
    , 0 AS ID2
    , "" AS Col3
    FROM CommonTable
    WHERE 0 UNION...

    It's a bit messy, but works and tricks Access into using the correct column types.

    If anyone knows a "correct" method for specifying a column type for NULL that would help make it a bit neater.

Posting Permissions

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