CODEWITHSUNDEEP
sqljoin
ajayramesh
ajayramesh

Reputation: 3784

How do i join same table and sort it in sql?

I have table like below one -

Name      Null     Type         
--------- -------- ------------ 
ID        NOT NULL NUMBER       
Name               VARCHAR2(20) 
PARENT_ID          NUMBER

Table contents 

ID        Name     PARENT_ID
--------- -------- ------------ 
1         Ramesh    null*
2         Ajay      1

I want to find out best SQL join query where I can populate the results like below.

For each row I want to know the ParentName not ID. How can I do that ?

ID        Name     ParentName
--------- -------- ------------ 
1         Ramesh    null*
2         Ajay      Ramesh

*null or blank

This is not example of the my requirement.

I have tried below SQL with left join but I am not sure if its the proper way.

SELECT S1.ID,S1.CRID AS PARENT_CRID,S2.CRID AS CRID  FROM DAJ_JOINS S1
left JOIN DAJ_JOINS S2
ON S1.ID=S2.PARENT_ID
order by id  asc;

Upvotes: 0

Views: 96

Answers (2)

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

With SQL Server 2005+, you can try:

SELECT *
FROM DAJ_JOINS D
  OUTER APPLY (SELECT Name As Parent_Name FROM DAJ_JOINS WHERE ID = D.Parent_ID) A

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Your query is close but the joining clause needs to be reversed as

select 
t1.ID,
t1.Name,
t2.Name as PARENT_Name
from DAJ_JOINS t1
left join DAJ_JOINS t2 on t1.PARENT_ID = t2.ID
order by t1.ID

Upvotes: 1

Related Questions