Joining tables issue

Results 1 to 2 of 2

Thread: Joining tables issue

  1. #1
    Join Date
    Dec 1969

    Default Joining tables issue

    This is a long question/problem i have. Using access 2000 and my problem is that when i am attempting to join multiple tables, if one(or more) of the join criteria isnt met (ie: 2 ID&#039;s are to be joined and one of the tables has no value for that field), the entire row is omitted. A good example is trying to join a product, employee, and product modify table. I want to see all products that have been modified as well as products that have not. The products that have not been modifed have nothing in the ModifiedBy fields (linked to the employees table). What I have done in the past, is to just grab multiple recordsets and then filter them to get the info to screen. So, heres my question: How can tables be joined so that results will be displayed if the join criteria is met, or if the join criteria is blank? 1 solution i came up with was to have the first record in every table be sort of a "default" record that is to simulate a null value. This doesnt seem like the correct way to do this. Maybe multiple recordsets is OK? <BR><BR>Thanks

  2. #2
    Join Date
    Dec 1969

    Default actually really basic

    use LEFT OUTER JOIN or RIGHT OUTER JOIN depending on from which table you want to see all the results and from which table only results if present.<BR><BR>SELECT * FROM table1 LEFT OUTER JOIN <BR>table2 ON table1.ID = table2.TID<BR><BR>this gets all rows from table 1, and those rows from table2 when the join criteria are met, if there are no mathing rows in table2 NULLS are retured for those rows.<BR>

Posting Permissions

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