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: Today @ 4:07 AM
Points: 904, Visits: 1,877
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:20 AM
Points: 2,379, Visits: 7,578
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 | Blog coming soon (for sufficiently large values of "soon" )!
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: Today @ 4:07 AM
Points: 904, Visits: 1,877
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
Posted Sunday, September 2, 2012 9:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:21 PM
Points: 1,945, Visits: 3,059
Many years ago, the ANSI X3H2 Committee invited Bjarne Stroustrup to speak to us on OO and how it would work with SQL. He was working for Bell Labs, which had the smartest people on Earth and a huge budget. He told us how they had tried to get objects into RDBMS.

They tried four different approaches and concluded that this is a truly awful idea. "No matter how far you have walked down the wrong road, turn back" --Turkish Proverb.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1353232
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse