Help with a query - self join on table

  • Im haveing a lot of trouble writing a query. Ive been spinning my wheels for over an hour, and cant figure out what im not seeing.

    Below is my data source. It is one table. Note each object has a distinct objectKey, but not a distinct ObjectID. I want to populate a column called "RootObjectKey. In this column i get the objectKey, based on the RootObjectID joining on the ObjectID (self join).

    The problem is that i need it retruned in this format, total of 17 rows. When i self join the table on objectID = RootObjectID 18 rows as objectID is represented twice.

    Has anyone any idea how i return the 16 columns, populated wihtthe correct ObjectKey in the RootObjectKey column?

    I hope that is clear enough to understand. thanks for any help.

    ObjectKeyObjectID RootObjectID RootObjectKey

    111

    221

    331

    441

    551

    661

    771

    881

    999

    101010

    111111

    121212

    131313

    141313

    151515

  • Hey,

    could you read this article[/url], then set-up the DDL and sample data in this format please? Then include the expected results based on your sample data.

    Thanks 😀


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi. Nevermind, i figured that what im trying to do is impossible and i need more info. i think some rows should be versioned, so i need to go to the source data to see if versioning column is available. thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply