How to get parent name from the parent id

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get parent name from the parent id

Author

Topic

shahid09
Starting Member

35 Posts

Posted - 2009-09-03 : 00:17:02

Hi All

Currently we have table like this :

quote:

PARENT_ID REQ_ID REQ_NAME
0 1 Parent X
1 2 Child A
1 3 Child B
1 4 Child C
0 5 Parent Y
5 6 Child D
5 7 child E

Is there any way i can add new column Parent Name where I can show REQ_NAME based on corresponding PARENT_ID.

quote:

PARENT_ID REQ_ID REQ_NAME Parent Name
0 1 Parent X Main Parent
1 2 Child A Parent X
1 3 Child B Parent X
1 4 Child C Parent X
0 6 Parent Y Main Parent
5 7 Child D Parent Y
5 8 child E Parent Y

Thanks in advance
SIK

ruchijani
Starting Member

23 Posts

Posted - 2009-09-03 : 00:41:57

try like this

SELECT *,
(CASE REQ_NAME
WHEN 'Parent X' THEN 'Main Parent'
WHEN 'Child A' THEN 'Parent X'
WHEN 'Child B' THEN 'Parent X'
WHEN 'Child C' THEN 'Parent X'
WHEN 'Parent Y' THEN 'Main Parent'
WHEN 'Child D' THEN 'Parent Y'
WHEN 'Child E' THEN 'Parent Y'
END) AS [Parent Name]
FROM TABLE

Thanks
Ruchi

shahid09
Starting Member

35 Posts

Posted - 2009-09-03 : 00:56:18

Ruchi,

Thanks for the answer. Actually we have 1000+ row of data. It will be ver difficult to give this kind of statment. Is there any automatic way to print all these vlaues in the column without using Case statement.

Thanks alot,
Shahid

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-03 : 01:04:03

Hi, Try this below method

DECLARE @T TABLE
(
Emp_ID VARCHAR(10),
[Name] VARCHAR(10),
ManagerID varchar(32)
)

INSERT INTO @T
SELECT '001', 'E1', '002'
UNION ALL SELECT '002', 'E2', '003'
UNION ALL SELECT '003', 'E3', '004'
UNION ALL SELECT '004', 'E4', NULL
UNION ALL SELECT '005', 'E5', '006'
UNION ALL SELECT '006', 'E6', NULL

;WITH cte ( emp_id,Name,managerid,path) AS
(
SELECT emp_id,Name,managerid,CONVERT(VARCHAR(MAX),emp_id) from @t where managerid is null
UNION ALL
SELECT t.emp_id,t.name,t.ManagerID,c.path+'-'+t.emp_id from @t t
inner join cte c on c.emp_id = t.managerid
)

SELECT emp_id,Name,managerid,
CASE WHEN CHARINDEX('-',path) = 0 THEN NULL ELSE LEFT(path,CHARINDEX('-',path)-1) END AS Manger
FROM cte ORDER BY emp_id

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-03 : 01:04:49

try this one
select t.*,ISNULL(s.req_name,'Main Parent') from @t t LEFT join @t s on s.REQ_ID = t.parent_id

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-03 : 01:14:32

Hi, the below query for u

DECLARE @T TABLE
(
managerid VARCHAR(10),
emp_id VARCHAR(10),
Name VARCHAR(32)
)

INSERT INTO @T
SELECT NULL, 1, 'Parent X' UNION ALL
SELECT 1, 2, 'Child A' UNION ALL
SELECT 1, 3, 'Child B' UNION ALL
SELECT 1, 4, 'Child C' UNION ALL
SELECT NULL, 5, 'Parent Y' UNION ALL
SELECT 5, 6, 'Child D' UNION ALL
SELECT 5, 7, 'Child E'

;WITH cte ( emp_id,Name,managerid,path) AS
(
SELECT emp_id,Name,managerid,CONVERT(VARCHAR(MAX),[Name]) from @t where managerid is null
UNION ALL
SELECT t.emp_id,t.name,t.ManagerID,c.Name+'-'+CONVERT(VARCHAR(MAX),t.[Name])e from @t t
inner join cte c on c.emp_id = t.managerid
)
SELECT emp_id,Name,
CASE WHEN CHARINDEX('-',path) = 0 THEN 'Main Parent' ELSE LEFT(path,CHARINDEX('-',path)-1) END AS Manger
FROM cte ORDER BY emp_id

shahid09
Starting Member

35 Posts

Posted - 2009-09-03 : 09:21:52

Hi,

Actually I come up with the following solution. Sub Query is working fine if i run independantly but When I use as a sub Query getting following error

"Only one expression can be specified in the select list when the sub query is not intoruced with EXIST.




SELECT  (Select A.RQ_REQ_NAME, (Select B.RQ_REQ_NAME From REQ B WHERE A.RQ_FATHER_ID = B.RQ_REQ_ID) AS "PArent Name"
FROM REQ A), REQ.RQ_FATHER_ID, REQ.RQ_REQ_ID, REQ.RQ_REQ_NAME,COUNT (REQ_COVER.RC_ITEM_ID) AS "Coverage" , REQ.RQ_REQ_AUTHOR, REQ.RQ_REQ_DATE + ' ' + REQ.RQ_REQ_TIME AS 'Creation Date'
FROM REQ
LEFT JOIN REQ_COVER
ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID
WHERE REQ.RQ_TYPE_ID IN (3,4) AND REQ.RQ_REQ_PATH LIKE 'AAAAAD%'
GROUP BY REQ.RQ_FATHER_ID, REQ.RQ_REQ_ID,REQ.RQ_REQ_NAME, REQ.RQ_REQ_AUTHOR, REQ.RQ_REQ_DATE, REQ.RQ_REQ_TIME
ORDER BY REQ.RQ_FATHER_ID

Thanks,
SIK

shahid09
Starting Member

35 Posts

Posted - 2009-09-03 : 09:30:47

I just realized i did some syntax mistakes. This problem has been resolved.

Thanks everyone

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-03 : 09:49:37

If your table is having 1000+ records then use of sub query is not good solution. You should try the approach provided by bkir above.

Rahul Shinde

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-03 : 12:06:35

Here is another way:

-- Using Nageswar9's Table

DECLARE @T TABLE 
(
Emp_ID VARCHAR(10), 
[Name] VARCHAR(10), 
ManagerID varchar(32)
)

INSERT INTO @T
SELECT '001', 'E1', '002'
UNION ALL SELECT '002', 'E2', '003'
UNION ALL SELECT '003', 'E3', '004'
UNION ALL SELECT '004', 'E4', NULL
UNION ALL SELECT '005', 'E5', '006'
UNION ALL SELECT '006', 'E6', NULL


SELECT
	A.*,
	COALESCE(B.Name, 'Main Parent') AS ParentName
FROM
	@T AS A
LEFT OUTER JOIN
	@T AS B
	ON A.Emp_ID = B.ManagerID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources