By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Select Columns using nested select statement.

P: 1
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?
Jan 21 '10 #1
Share this Question
Share on Google+
3 Replies


code green
Expert 100+
P: 1,726
It looks a little unconventional, and the sub-query would throw an error if there was more than one ID returned. What about
Expand|Select|Wrap|Line Numbers
  1. SELECT child.ID, child.Name, child.Desc, child.ParentID, 
  2. parent.Name AS ParentName,
  3. parent.Desc AS ParentDesc
  4. FROM TableA child
  5. JOIN TableA parent ON child.ID = parent.ParentID;
  6.  
Pleas adjust to correct parent-child field required
Jan 21 '10 #2

ck9663
Expert 2.5K+
P: 2,878
Try using a recursive CTE instead.

Happy Coding!!!

~~ CK
Jan 21 '10 #3

nbiswas
100+
P: 149
Recursive CTE

Simple Example of Recursive CTE
Mar 24 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.