Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    8

    Unhappy Unanswered: Outer Join using sub-query

    I have to do an outer join to a table using more than one
    set of criteria. The join to table "a" has to be a left outer join
    because the record in tables "mt" "dt" does not have to have an
    account. However, if the record in tables "mt" "dt" does have
    an account, the account has to be matched to the account
    that was current at the time of the transaction. The query
    below returns multiple records because of the sub-query
    which is used to collect the "current" account. How would
    I organize the query so that it can find the correct account
    and at the same time be an outer join?

    select mt.account_transaction_id at_id,
    dt.account_transaction_id sat_at_id,
    mt.trans_date at_trans_date,
    dt.trans_date sat_trans_date,
    mt.trans_amount at_trans_amt,
    dt.trans_amount sat_trans_amt,
    a.account_id
    from cis.main_transaction mt,
    cis.detail_transaction dt,
    cis.account a,
    where mt.trans_date >= '18-sep-2002'
    and mt.trans_date < '19-sep-2002'
    and mt.main_transaction_id = dt.main_transaction_id (+)
    and dt.account_id = a.account_id (+)
    and a.effective_date = (select max(a2.effective_date)
    from cis.account a2
    where a2.account_id = dt.account_id
    and a2.effective_date <= dt.trans_date )

    Thank you in advance for any help,

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Outer Join using sub-query

    Would something like this do it?

    and
    ( a.account_id IS NULL
    OR a.effective_date = (select max(a2.effective_date)
    from cis.account a2
    where a2.account_id = dt.account_id
    and a2.effective_date <= dt.trans_date )
    )

    i.e. if a.account_id IS NULL then outer join didn't find a match, so don't test the date; else test the date.

  3. #3
    Join Date
    Nov 2002
    Posts
    8
    Thanks for the reply.

    What I actually did was make it simple. I am moving my transactions
    into a temp table and adding the missing info in a second step.
    It is fast and will be simple to maintain.

  4. #4
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    31
    well, i'd like to say thanks

    i encountered just this problem and your solution was precisely what i needed as i was unable to simplify further.

    cheers

    david k
    My homepage:
    http://www.buro9.com/
    My work:
    http://www.btopenworld.com/
    http://www.officialfootballsites.co.uk/
    http://www.jeepster.co.uk/

Posting Permissions

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