Results 1 to 6 of 6

Thread: Self Join

  1. #1
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71

    Unanswered: Self Join

    Hello!

    I have a problem, look at this TableA and read my question the after

    Account ManTyp Document Portvalue
    100040 MCS ID Document 425.2200
    100040 MCS Utiltity Bill 425.2200
    100085 MCS ID Document 2582.6200
    1000504 MCS ID Document 36086.6100
    1000504 MCS Utiltity Bill 36086.6100
    1000512 MCS ID Document 195713.2600
    1000512 MCS Utiltity Bill 195713.2600
    1000555 MCS Utiltity Bill 22223.6200

    I want to return one record per account in a table

    Account ManTyp Document1 Document2 Portvalue
    100040 MCS ID Document Utiltity Bill 425.2200
    100085 MCS ID Document 2582.6200
    1000504 MCS ID Document Utiltity Bill 36086.6100
    1000512 MCS ID Document Utiltity Bill 195713.2600
    1000555 MCS Utiltity Bill 22223.6200


    In otherwords i want column document to be divided into to colums, document1(to contain ID Documents) and document2 (to contain Utility Bill)

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select V1.*, ISNULL(V2.document, 'NA')
    from
    (select ta.*
    from tableA ta
    where document = 'Document') V1
    LEFT OUTER JOIN
    (select ta.id, ta.document
    from tableA ta
    where document = 'Utility') V2
    ON V1.ID = V2.ID;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2003
    Location
    South Africa
    Posts
    71
    With your message i don't get results back because ID is incremental, so ID in V1 won't be available in V2

    select *
    from
    (select ta.*
    from tableA ta
    where document = 'Document') V1
    INNER JOIN
    (select ta.id, ta.document
    from tableA ta
    where document = 'Utility') V2
    ON V1.ID = V2.ID;

  4. #4
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    You have to use a FULL OUTER JOIN instead of a LEFT OUTER JOIN, then you will get back all records.

    Carsten

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A crosstab query. Look it up in Books On Line.

    select Account,
    ManTyp,
    max(Case Document when "ID Document" then Document else Null end) as Document1,
    max(Case Document when "Utility Bill" then Document else Null end) as Document2,
    Portvalue
    from TableA
    group by Account, ManTyp, Portvalue
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You want this

    http://www.sqlteam.com/item.asp?ItemID=2955

    Or wait for YUKON
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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