DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Need help with tricky inner select on SQL

  1. #1
    Join Date
    Sep 2006
    Location
    Saint Louis, Missouri
    Posts
    2

    Need help with tricky inner select on SQL

    First, I have 3 tables:
    TAX_REF
    TAX_CODE
    PAYER_CODE
    TAX_TYPE

    ACCT_TAX_REF
    TAX_CODE
    PAYER_CODE
    ACCT_NBR

    ACCT_MSTR
    ACCT_NBR
    ACCT_DESC

    For the resultset I return, I would like to have the following columns:
    TAX_CODE
    PAYER_CODE
    TAX_TYPE
    EMP_ACCT_NBR
    EMP_ACCT_DESC
    EMPR_ACCT_NBR
    EMPR_ACCT_DESC

    Here's the scoop:
    I need ALL the records from the TAX_REF table. I need to link to the ACCT_TAX_REF table by TAX_CODE and PAYER_CODE in order to retrieve the ACCT_NBR. Further I have to link to the ACCT_MSTR in order to also bring back the ACCT_DESC for the ACCT_NBR. The problem is with the PAYER_CODE field.

    TAX_REF.PAYER_CODE can have values of E, R, or B. The ACCT_TAX_REF.PAYER_CODE can only have values of E or R. Therefore I cannot just do a simple join.

    What I want to happen:
    If the TAX_REF.PAYER_CODE = E, take the ACCT_TAX_REF.ACCT_NBR and put it into the returning resultset’s EMP_ACCT_NBR field.

    If the TAX_REF_PAYER_CODE = R, take the ACCT_TAX_REF.ACCT_NBR and put it into the returning resultset’s EMPR_ACCT_NBR field.

    If the TAX_REF.PAYER_CODE = B, I need the query to get both the E and the R values from the ACCT_TAX_REF table’s PAYER_CODE (for that TAX_CODE) and put the ACCT_NBR into the matching EMP_ACCT_NBR (if E) or EMPR_ACCT_NBR (if R) fields.

    I hope I’ve explained this well. I know there should be some kind of inner select, but I’m not sure how to go about it with that “B” code throwing a monkey wrench into the mix.
    Thanks for your help.


    Here’s one attempt:

    Select t.TAX_CODE,
    t.PAYER_CODE,
    t.TAX_TYPE,
    nvl (min (b.ACCT_NBR),0) AS EMP_ACCT_NBR,
    c.ACCT_DESC AS EMP_ACCT_DESC
    from TAX_REF t
    left outer join ACCT_TAX_REF b on t.TAX_CODE = b.TAX_CODE and
    t.PAYER_CODE = b.PAYER_CODE
    left outer join ACCT_MSTR c on b.ACCT_NBR = c.ACCT_NBR
    group by t.TAX_CODE,
    t.PAYER_CODE,
    t.TAX_TYPE,
    EMP_ACCT_DESC

    That, of course, doesn’t put my “R” values into the EMPR_ACCT_NBR and EMPR_ACCT_DESC fields though. Nor does it handle the “B” scenario. Help.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I'm v.confused . . .

    Is this the join condition?
    on t.TAX_CODE = b.TAX_CODE
    and (t.PAYER_CODE = b.PAYER_CODE OR t.PAYER_CODE = 'B')
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Sep 2006
    Location
    Saint Louis, Missouri
    Posts
    2
    The join is indeed t.TAX_CODE = b.TAX_CODE with the conditional join on PAYER_CODE. If the value of PAYER_CODE is either E or R, then all is fine, but if t.PAYER_CODE = B, then I need to go the the ACCT_TAX_REF table twice basically so I can retrieve both the PAYER_CODE values of E and R for that matching TAX_CODE.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use Inner SQL at col level.

    Code:
    select a.c1, a.c2,
             (select b.c1 from table b
               where b.c2 = a.c2 and other condition),
             (select c.c3 from table c
               where c.c3 = a.c3 and other condition)
    from table a
    /
    Also read this:

    http://tkyte.blogspot.com/2005/06/ho...questions.html

    Tamil

Posting Permissions

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


Click Here to Expand Forum to Full Width