I expecting result like that please help me

Posted by Johnseelan under Sql Server on 10/19/2012 | Points: 10 | Views : 1018 | Status : [Member] | Replies : 6
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

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

Up
0
Down
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

Login to post response