I expecting result like that please help me

Posted by Johnseelan under Sql Server on 10/19/2012 | Points: 10 | Views : 1843 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Definitions:

CREATE TABLE [NAMES]
(
[ID] [int],
[NAME] [varchar](100)
)

CREATE TABLE [RELATIONSHIPS]
(
[NAMEID] [int],
[PARENT_NAMEID] [int]
)

Sample Data:

INSERT [NAMES] VALUES (1,'FRANK')
INSERT [NAMES] VALUES (2,'JO')
INSERT [NAMES] VALUES (3,'MARY')
INSERT [NAMES] VALUES (4,'PETER')
INSERT [NAMES] VALUES (5,'MAY')

INSERT [RELATIONSHIPS] VALUES (1,0)
INSERT [RELATIONSHIPS] VALUES (2,1)
INSERT [RELATIONSHIPS] VALUES (3,2)
INSERT [RELATIONSHIPS] VALUES (4,1)
INSERT [RELATIONSHIPS] VALUES (5,2)

Expected Result:

LEVEL ID NAME
0 1 FRANK
1 2 JO
2 3 MARY
2 5 MAY
1 4 PETER

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Ranjeet_8 on: 10/19/2012 [Member] [MVP] Gold | Points: 25

0	You can use INNER JOIN Select T2.[LEVEL],T1.ID,T1.NAME From [NAMES] T1 Inner join [RELATIONSHIPS] T2 ON T1.ID=T2.ID Johnseelan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Wadhwanisanju on: 10/19/2012 [Member] Starter | Points: 25

0	Hi, select PARENT_NAMEID as [Level], ID,NAME from NAMES inner join RELATIONSHIPS on ID=NAMEID Just execute this and you will get your desired result. mark as ans if this correct. Johnseelan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Vasanthmvp on: 10/19/2012 [Member] Starter | Points: 25

0	Hi Use joins, Select R.PARENT_NAMEID AS [LEVEL], N.ID AS [ID], N.NAME AS [NAME] from NAMES AS N JOIN RELATIONSHIPS AS R ON N.ID = R.NAMEID Awesome Coding !! :) Johnseelan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Johnseelan on: 10/19/2012 [Member] Starter | Points: 25

0	Expected Result: LEVEL ID NAME 0 1 FRANK 1 2 JO 2 3 MARY 2 5 MAY 1 4 PETER hi all see my expected result Johnseelan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jayakumars on: 10/20/2012 [Member] [MVP] Bronze | Points: 25

0	hi johnseelan try this Query Select Parent_NameId as LEVEL, NameID as ID,NAme from [NAMES] NA,[RELATIONSHIPS] RL where NA.ID=RL.NAMEID Order By NAme mark as ans if this correct. Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Johnseelan, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sourabh07 on: 10/20/2012 [Member] Starter | Points: 25

0	hi.... please try this query....and see the result... Query : Select B.Parent_Nameid as Level, A.Id, A.Name From Names A Inner Join Relationships B on A.Id=B.NameId order by A.Name Output: LEVEL ID NAME 0 1 FRANK 1 2 JO 2 3 MARY 2 5 MAY 1 4 PETER Sourabh07 Johnseelan, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts