sqlserver self join - optimal way?

Question

I have written the below query. I do not think it is the optimum way. The scenario is - given a child, I want to fetch all rows corresponding to its parent(root).

select * from TableA where column_root = 
(select column_root from TableA where column_child = 123)

Raging Bull · Accepted Answer · 2015-07-22T10:21:01.547

0

You can use JOIN:

select T1.* from 
    TableA T1 join
    TableA T2 ON T1.column_root=T2.column_root
where T2.column_child = 123

Sample result in SQL Fiddle

edited Jul 22 '15 at 10:21

answered Jul 22 '15 at 10:05

Raging Bull

18,113
13
47
53

In my scenario, if a child has more than 1 root, that would mean there is some data issue. – KeenUser Jul 22 '15 at 10:07
@AdarshK: So you are saying, there will be only one record with column_child=123? Or there will be only one column_root for child 123? – Raging Bull Jul 22 '15 at 10:08
Both. only unique child records, for a given child record only one root. – KeenUser Jul 22 '15 at 10:11
@AdarshK: Then why not `select * from TableA where column_child = 123`?? – Raging Bull Jul 22 '15 at 10:12
That gives only one record, where as i need all children under the record 123's parent_root. – KeenUser Jul 22 '15 at 10:18
@AdarshK: Updated my answer. Try it! Also see the fiddle demo. – Raging Bull Jul 22 '15 at 10:26
@AdarshK: See [**this post**](http://stackoverflow.com/questions/1001543/in-vs-join-with-large-rowsets). – Raging Bull Jul 22 '15 at 11:05

sqlserver self join - optimal way?

1 Answers1