Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outer Join Problem (2000)

    Aloha All,

    Sorry, this is a long post but to me this complicated problem and I want to make sure I

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Outer Join Problem (2000)

    > Tells me it can

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

    Re: Outer Join Problem (2000)

    As Jefferson pointed out, you have tblPasFile in your query but without a join to any other table. That means you will get a cartesian product of rows, all the rows in the other two tables that meet the criteria *times* the number of rows in tblPasFile. However, that shouldn't cause the ambiguous outer join message, since it isn't an outer join. Start by removing that table.

    What you want between the tables is an outer join, not an inner join. What you're telling it here:

    tblUTC_task INNER JOIN tblAUMD ...

    is to return all records that match the criteria in both tables. If you want all records from tblAUMD and only the matching records from tblTC_task, then you need to change the join to

    tblUTC_task RIGHT JOIN tblAUMD ...

    Try that and see if you get the right records returned. If you were planning to use the query as an update query, you have a different problem. Let's make sure you get the records you want first and then we can address the update part.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Feb 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outer Join Problem (2000)

    Jefferson and Charlotte,

    Thanks for help. Sorry, I thought took the tblPasFile table out of the query. I didn't want to confuse the problem. I did take it out and re-run it. Same results.

    Let me back up and give a brief overview of the db. tblAUMD is a employee db. It holds attributes of all the positions in the company. The PAS field is a unique location identifier, AFSC describes the job (ie electrician, plumber, ect.), category describes the type of employee (i.e. full time, part time). The tblUTC table is a generic sub-set of the tblAUMD and tells me how many of a give AFSC I need from a location for a specific task. So at location A the tblUTC table will tell me I need 10 electricians but there may be 20 at that location. I want the query to match the first 10 and ignore the rest. So, I want match all 12,000 records in tblUTC by PAS, AFSC and category to the tblAUMD and return only those records.

    I was trying to do this with an outer join but when I set the join properties I would get the ambiguous outer join message. After reading your message I tried a right join and a left join. Neither produced the result I am looking for. The right join returns almost 50,000 records and the UTC data field is blank. The left join returns 1143 records and only the UTC data field contains data.


    SELECT DISTINCT tblAUMD.ORG_PAS_CD, tblUTC_task.UTC, tblAUMD.AFSC, tblAUMD.CATEGORY, tblAUMD.RQMNT_PSTN_ID
    FROM tblUTC_task right JOIN tblAUMD ON (tblUTC_task.AFSC = tblAUMD.AFSC) AND (tblUTC_task.PAS = tblAUMD.ORG_PAS_CD) AND (tblUTC_task.CAT = tblAUMD.CATEGORY) AND (tblUTC_task.GRADE_ABV_CD = tblAUMD.GRADE_ABV_CD)
    GROUP BY tblAUMD.ORG_PAS_CD, tblUTC_task.UTC, tblAUMD.AFSC, tblAUMD.CATEGORY, tblAUMD.RQMNT_PSTN_ID
    ORDER BY tblAUMD.ORG_PAS_CD, tblUTC_task.UTC, tblAUMD.AFSC, tblAUMD.RQMNT_PSTN_ID;

    Any ideas?

Posting Permissions

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