Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Join types (2000)

    Anyone bold enough to attempt to describe the various join types.. in plain english? I always built queries using the QBE, but have come to the conclusion that it is essential to have this knowledge, and I'll probably move to creating queries using SQL

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Join types (2000)

    If you double click a join in a query, the dialog gives a pretty good description of what the three options do. Which part is not clear to you?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join types (2000)

    I thought there were more options? What does Left Inner and Right Outer mean?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Join types (2000)

    There are three kinds of joins, corresponding to the three options in the Join Properties dialog: Inner, Left (Outer) and Right (Outer). Say you have two tables A and B, joined on a field.

    The first option displays only those records for which the linked fields in both tables are equal (and non-blank). This is called an inner join.
    The second option displays ALL records from table A. For records in table A that have a match in table B, the fields from the matching records in table B are displayed; for records in table A that have no match in B, the fields from table B are left blank. This is called an outer join, to be more precise a left (outer) join from A to B (or a right join from B to A.)
    The third option displays ALL records from table B. For records in table B that have a match in table A, the fields from the matching records in table A are displayed; for records in table B that have no match in A, the fields from table A are left blank. This is called an outer join, to be more precise a right (outer) join from A to B (or a left join from B to A.)

    There is no Left Inner join, and Right Outer join is the same as a Right join.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join types (2000)

    So in SQL the use of Right or Left is just determined by the order in which you declare the tables in the statement?

    When Access is unable to process a query (which happens fairly often) and you get HELPFUL error messages, I always assume that I'm asking Access to do something which doesn't make sense. Do you find sketching out the table contents helpful in determining where the query has gone wrong? Of course, this process is more complicated when you have one query running on top of another.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Join types (2000)

    Yes, left and right joins are essentially the same, with the order of the tables interchanged.

    If Access indicates that a query is not valid, you should look at the type of join, at the nature of the linking fields on both sides of the join, etc.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join types (2000)

    I was thinking that I could scribble a small 'map' of the query. That is, jot down a couple of sample rows from one table, and follow the links to other tables, and hopefully I would begin to see where a discrepancy lies. Eventually I assume I would see that Access cannot follow the links through to a unique row?! Do you think this might help?

    I think a straight forward indicator is that if a table has an arrow pointing to it from the left AND from the right then this query cannot be resolved. Is this true?

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Join types (2000)

    That is correct, because you've created ambiguous joins. Outer joins have to go in the same direction, you can't reverse along the way, because in an outer join, the table on the "one" side (left or right) is driving the query.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Join types (2000)

    The first point is a matter of personal preference; whatever works for you is fine.

    The latter is true, yes, but there are other situations, too, in which Access complains about ambiguous outer joins.

Posting Permissions

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