Child + Parent Reference Sql

I am trying to write a query to display the name of the project and the name of the parent project, but haven't come with the proper way so far. CREATE TABLE project (p_id NUMBER(6

Solution 1:

You need a self join, joining the PROJECT table to itself like this

SELECT p.p_id, 
       p.project_name, 
       p.parent_p_id, 
       pp.project_name as parent_project
FROM project p
     inner join project pp
     on p.parent_p_id = pp.p_id;

If you want to include projects which don't have a parent project then the join would be a LEFT OUTER JOIN.