1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need Help writing query joining table to itself multiple times

Discussion in 'SQL PL/SQL' started by sfs, Sep 9, 2011.

  1. sfs

    sfs Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have to write a query for a client to display business officers' names and title along with the business name
    The table looks like this
    AcctNumber
    OfficerTitle
    OfficerName
    RecKey

    90% of the businesses have exactly 4 officer records, although some have less and some have more.
    There is a separate table that has the AcctNumber, BusinessName about 30 other fields that I don’t need
    An individual account can have 30 or 40 records on the other table.

    The client wants to display 1 record per account.

    Initially I wrote a query to join the table to itself:

    Select A.OfficerTtitle, A.OfficerName, B.OfficerTitle, B.OfficerName, C.OfficerTtitle, C.OfficerName, D.OfficerTitle, D.OfficerName where A.AcctNumber = B.AcctNumber and A.AcctNumber = C.AcctNumber and A.AcctNumber = D.AcctNumber

    This returned tons of duplicate rows for each account ( number of records * number of records, I think)
    So added

    And A.RecKey > B.RecKey and B.RecKey > C. RecKey and C.RecKey . D.RecKey

    This works when there are exactly 4 records per account. If there are less than 4 records on the account it skips the account and if there are more than 4 records, it returns multiple rows.

    But when I try to l join this to the other table to get the business name, I get a row for every record on the other table
    I tried select distinct on the other table and the query runs for ever and never returns anything
    I tried outer joins and subqueries, but no luck so far. I was thinking maybe a subquery - if exists - because I don't know how many records there are on an account, but don't know how to structure that

    Any suggestions would be appreciated
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post what you have written so we can see where the problem may be.
     
  3. sfs

    sfs Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    thanks for the response.

    I've made numerous attempts to code this, these are the ones that produced some results
    This one skips all accounts where there are less than 4 owners and returns a row for every record in table E:

    SELECT A.ACCT_NBR as "Account Number" , E.DBA_TXT AS "Doing Business As Name",
    (A.OWNER_FIRST_NM ||' '|| A.OWNER_MIDDLE_NM ||' '|| A.OWNER_LAST_NM ||' '|| A.OWNER_SUFFIX_TXT) AS "First Owner Name",A.TITLE AS " First Owner Title",
    (B.OWNER_FIRST_NM ||' '|| B.OWNER_MIDDLE_NM ||' '|| B.OWNER_LAST_NM ||' '|| B.OWNER_SUFFIX_TXT) AS "Second Owner Name",B.TITLE AS " Second Owner Title" ,
    (C.OWNER_FIRST_NM ||' '|| C.OWNER_MIDDLE_NM ||' '|| C.OWNER_LAST_NM ||' '|| C.OWNER_SUFFIX_TXT) AS "Third Owner Name",C.TITLE AS " Third Owner Title",
    (D.OWNER_FIRST_NM ||' '|| D.OWNER_MIDDLE_NM ||' '|| D.OWNER_LAST_NM ||' '|| D.OWNER_SUFFIX_TXT) AS "Fourth Owner Name",D.TITLE AS "Fourth Owner Title"
    from LIC_OWNERSHIP_INFO_VW A , LIC_OWNERSHIP_INFO_VW B, LIC_OWNERSHIP_INFO_VW C, LIC_OWNERSHIP_INFO_VW D, LIC_ACCOUNT_INFO_VW E
    where A.ACCT_NBR = B.ACCT_NBR AND A.ACCT_NBR = C.ACCT_NBR AND A.ACCT_NBR = D.ACCT_NBR AND A.ACCT_NBR = E.ACCT_NBR
    AND A.OWNERSHIP_ID > B.OWNERSHIP_ID AND B.OWNERSHIP_ID > C.OWNERSHIP_ID AND C.OWNERSHIP_ID > D.OWNERSHIP_ID
    order by a.acct_nbr

    if I add
    SELECT DISTINCT to E.DBA_TXT AS "Doing Business As Name",
    the query runs for every and never returns

    I tried do a Left Outer join, this picks up the accounts with less than 4 owner records, but still shows multiple records per account


    SELECT A.ACCT_NBR as "Account Number" , E.DBA_TXT AS "Doing Business As Name",
    (A.OWNER_FIRST_NM ||' '|| A.OWNER_MIDDLE_NM ||' '|| A.OWNER_LAST_NM ||' '|| A.OWNER_SUFFIX_TXT) AS "First Owner Name",A.TITLE AS " First Owner Title",
    (B.OWNER_FIRST_NM ||' '|| B.OWNER_MIDDLE_NM ||' '|| B.OWNER_LAST_NM ||' '|| B.OWNER_SUFFIX_TXT) AS "Second Owner Name",B.TITLE AS " Second Owner Title" ,
    (C.OWNER_FIRST_NM ||' '|| C.OWNER_MIDDLE_NM ||' '|| C.OWNER_LAST_NM ||' '|| C.OWNER_SUFFIX_TXT) AS "Third Owner Name",C.TITLE AS " Third Owner Title",
    (D.OWNER_FIRST_NM ||' '|| D.OWNER_MIDDLE_NM ||' '|| D.OWNER_LAST_NM ||' '|| D.OWNER_SUFFIX_TXT) AS "Fourth Owner Name",D.TITLE AS "Fourth Owner Title"
    FROM LIC_OWNERSHIP_INFO_VW A
    LEFT OUTER JOIN LIC_OWNERSHIP_INFO_VW B ON A.ACCT_NBR = B.ACCT_NBR AND A.OWNERSHIP_ID < B.OWNERSHIP_ID
    LEFT OUTER JOIN LIC_OWNERSHIP_INFO_VW C ON B.ACCT_NBR = C.ACCT_NBR AND B.OWNERSHIP_ID < C.OWNERSHIP_ID
    LEFT OUTER JOIN LIC_OWNERSHIP_INFO_VW D ON C.ACCT_NBR = D.ACCT_NBR AND C.OWNERSHIP_ID < D.OWNERSHIP_ID
    LEFT OUTER JOIN LIC_ACCOUNT_INFO_VW E ON E.ACCT_NBR = B.ACCT_NBR