Select Columns using nested select statement.

[bkoh]

Hi all,

I am populating data from a table which contain parent id which reference to itself
E.g.: TableA - ID, Name, Desc, ParentID
ParentID reference to ID

I would need the result to return:
ID, Name, Desc, ParentID, ParentName, ParentDesc

Select statement:
SELECT ID, Name, Desc, ParentID,
(SELECT Name from TableA WHERE ID=ParentID) AS ParentName,
(SELECT Desc from TableA WHERE ID=ParentID) AS ParentDesc
FROM TableA;

Is there any performance issue using this approach?
Is there a better way of doing this?

[code green]

It looks a little unconventional, and the sub-query would throw an error if there was more than one ID returned. What about

SELECT child.ID, child.Name, child.Desc, child.ParentID, 
parent.Name AS ParentName,
parent.Desc AS ParentDesc
FROM TableA child
JOIN TableA parent ON child.ID = parent.ParentID;
 

Pleas adjust to correct parent-child field required

[ck9663]

Try using a recursive CTE instead.

Happy Coding!!!

~~ CK

[nbiswas]

Recursive CTE

Simple Example of Recursive CTE