how to get relations using query?

Posted by aswinialuri-19361 under Sql Server on 11/21/2013 | Points: 10 | Views : 911 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

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

0	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

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

Latest Posts