CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Walter Henrike
Walter Henrike

Reputation: 1

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),
project_name VARCHAR2(30),
client_id NUMBER(6),
mgr_id NUMBER(6),
parent_p_id NUMBER(6),
CONSTRAINT project_pid_pk PRIMARY KEY (p_id),
CONSTRAINT project_client_id_fk FOREIGN KEY (client_id) REFERENCES client(client_id),
CONSTRAINT project_mgr_id_fk FOREIGN KEY (mgr_id) REFERENCES consultant(c_id));

ALTER TABLE project
ADD CONSTRAINT project_parent_pid_fk FOREIGN KEY (parent_p_id) REFERENCES project(p_id);

By using: 

SELECT project.p_id, project.project_name, project.parent_p_id
FROM project
WHERE project.parent_p_id IS NOT NULL;

I can get most of the information, but I don't know how to link the project.parent_p_id to the project.project_name

Care anyone to help me out?!

Thanks in advance :-)

Upvotes: 0

Views: 83

Answers (1)

APC
APC

Reputation: 146309

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.

Upvotes: 1

Related Questions