Help with a query - self join on table

Question

Help with a query - self join on table

winston Smith

SSCoach

Points: 19500
More actions
August 31, 2012 at 4:27 am

#393063

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

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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/

winston Smith SSCoach Points: 19500 More actions · Answer 2

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!