how to get relations using query?

Posted by aswinialuri-19361 under Sql Server on 11/21/2013 | Points: 10 | Views : 745 | Status : [Member] | Replies : 5
i have a table like
father child
anr nag
nag chaitanya
krishna mahesh
mahesh gautham

i need output like this:
grandfather child
anr chaitanya
krishna gautham


give me any suggestion about this

Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri



Responses

Posted by: Bandi on: 11/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
use recursive CTEs in the SQL Server

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

aswinialuri-19361, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Here you go ahead....

SELECT t1.father grandfather, t1.child as father, t2.child as son

FROM tableName t1
JOIN tableName t2 ON t1.child = t2.father


Mark as Answer

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

aswinialuri-19361, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: aswinialuri-19361 on: 12/3/2013 [Member] Starter | Points: 25

Up
0
Down
hi Bandi,
can you give me a clear explanation with example


Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri

aswinialuri-19361, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
if there is only two level relationship(son-father-grandfather)..... query with one self join is enough....

If that is the case use the above query..
Otherwise we have to go ahead with recursive query... Later I will explain clearly

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

aswinialuri-19361, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SELECT t1.father grandfather, t1.child as father, t2.child as son
FROM tableName t1
JOIN tableName t2 ON t1.child = t2.father -- Here am getting grandfather relation

you have to replace tableName with your table. Self-join
T1 alias represents anr is father of nag.
T2 alias represents nag is father of chaitanya.
So we are displaying t1.father as grandfather, t1.child as father and t2.child as son

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

aswinialuri-19361, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response