Results 1 to 8 of 8

Thread: table join

  1. #1
    Join Date
    Mar 2005
    Location
    Valladolid, Spain
    Posts
    110

    Angry Unanswered: table join

    Hi, I got a table (BANK) that have a primary key composed by three columns (id_organization, sco_id_person and sco_or_account) I want a result set that brings me just one register by person (sco_id_person). The problem is that some persons have more than one organization and when I do the join I get some sco_id_person repeats.


    The sql i did is:

    SELECT
    UNIQUE(A.SCO_ID_PERSON),
    A.SCO_ID_BANK_BRANCH,
    A.SCO_ACCOUNT_NUMBER
    FROM
    BANK A
    , (SELECT
    SCO_ID_PERSON, MAX(SCO_OR_ACCOUNT) AS SCO_OR_ACCOUNT
    FROM
    BANK
    GROUP BY SCO_ID_PERSON
    ) B
    WHERE
    A.SCO_ID_PERSON = B.SCO_ID_PERSON
    AND A.SCO_OR_ACCOUNT = B.SCO_OR_ACCOUNT

    p.s: sco_or_account is a field that contains a number, and each time a register is updated, a new row is inserted with a higher sco_or_account, so a history is kept and for my query i need the last rows (max())
    Juan Norton Alea
    DB2 v8.2 for win2k3 (fixpack 9)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you didn't say what the problem is

    i'm going to guess that it's the UNIQUE() function, which i've never heard of

    Code:
    SELECT A.SCO_ID_PERSON
         , A.SCO_ID_BANK_BRANCH
         , A.SCO_ACCOUNT_NUMBER 
      FROM BANK A
     where A.SCO_OR_ACCOUNT  
         = ( SELECT MAX(SCO_OR_ACCOUNT)  
               FROM BANK
              where SCO_ID_PERSON
                  = A.SCO_ID_PERSON )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2005
    Location
    Valladolid, Spain
    Posts
    110
    Quote Originally Posted by r937
    you didn't say what the problem is

    i'm going to guess that it's the UNIQUE() function, which i've never heard of

    Code:
    SELECT A.SCO_ID_PERSON
         , A.SCO_ID_BANK_BRANCH
         , A.SCO_ACCOUNT_NUMBER 
      FROM BANK A
     where A.SCO_OR_ACCOUNT  
         = ( SELECT MAX(SCO_OR_ACCOUNT)  
               FROM BANK
              where SCO_ID_PERSON
                  = A.SCO_ID_PERSON )

    The problem is that I got rows with the same sco_id_person and that is because they belong to other organization (id_organization).

    I want all the persons but just once.

    Thanks
    Juan Norton Alea
    DB2 v8.2 for win2k3 (fixpack 9)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, yes, sorry, i understand now (should have read your question more closely)

    what else besides the MAX(SCO_OR_ACCOUNT) do you need to see for each person?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2005
    Location
    Valladolid, Spain
    Posts
    110
    Quote Originally Posted by r937
    okay, yes, sorry, i understand now (should have read your question more closely)

    what else besides the MAX(SCO_OR_ACCOUNT) do you need to see for each person?

    I need to see in the result set the sco_id_person, sco_bank_branch and sco_account number but just one line per person (sco_id_person) because the problem is that as I told you that are many rows for each person because the belong to differents organizations (id_organization) and everytime the information is updated a new line is inserted (sco_or_account plus 1)

    Many thanks
    Juan Norton Alea
    DB2 v8.2 for win2k3 (fixpack 9)

  6. #6
    Join Date
    Mar 2005
    Location
    Valladolid, Spain
    Posts
    110
    Quote Originally Posted by r937
    okay, yes, sorry, i understand now (should have read your question more closely)

    what else besides the MAX(SCO_OR_ACCOUNT) do you need to see for each person?

    I need to see in the result set the sco_id_person, sco_bank_branch and sco_account number but just one line per person (sco_id_person) because the problem is that as I told you that are many rows for each person because the belong to differents organizations (id_organization) and everytime the information is updated a new line is inserted (sco_or_account plus 1)

    Many thanks
    Juan Norton Alea
    DB2 v8.2 for win2k3 (fixpack 9)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i understand the "one line per person" part

    now i ask you: if the person belongs to different organizations, which sco_bank_branch and sco_account number do you want to see?

    or are the sco_bank_branch and sco_account number the same for all organizations that a person belongs to?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2005
    Location
    Valladolid, Spain
    Posts
    110
    Quote Originally Posted by r937
    okay, i understand the "one line per person" part

    now i ask you: if the person belongs to different organizations, which sco_bank_branch and sco_account number do you want to see?

    or are the sco_bank_branch and sco_account number the same for all organizations that a person belongs to?

    Yes, you are right, sorry I didn`t mention it. Both sco_account_number and sco_bank_branch have differents values for the same person, in fact I do not care wich one to take, just one.

    Many thanks for your time!
    Juan Norton Alea
    DB2 v8.2 for win2k3 (fixpack 9)

Posting Permissions

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