Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with a query - self join on table Expand / Collapse
Author
Message
Posted Friday, August 31, 2012 4:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 20, 2015 9:03 AM
Points: 925, Visits: 1,974
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.


ObjectKey ObjectID RootObjectID RootObjectKey
1 1 1
2 2 1
3 3 1
4 4 1
5 5 1
6 6 1
7 7 1
8 8 1
9 9 9
10 10 10
11 11 11
12 12 12
13 13 13
14 13 13
15 15 15



Post #1352684
Posted Friday, August 31, 2012 4:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:58 AM
Points: 2,458, Visits: 7,949
Hey,

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

Thanks



Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn
Post #1352690
Posted Friday, August 31, 2012 5:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 20, 2015 9:03 AM
Points: 925, Visits: 1,974
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!
Post #1352714
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse