How to select a same column from a specialized table in PostgreSQL?-CodePudding

I have this table

CREATE TABLE Person(
    IDPerson VARCHAR(10),
    name VARCHAR(50),
    PRIMARY KEY(IDPerson)
);

Person table is a general table which has these 2 specialization:

CREATE TABLE Physician(
    IDPhysician VARCHAR(10),
    specialty VARCHAR(50),
    PRIMARY KEY(IDPhysician),
    FOREIGN KEY(IDPhysician) REFERENCES Person(IDPerson)
);
CREATE TABLE Volunteer(
    IDVolunteer VARCHAR(10),
    IDSupervisor VARCHAR(10),
    skill VARCHAR(30),
    PRIMARY KEY(IDVolunteer),
    FOREIGN KEY(IDVolunteer) REFERENCES Person(IDPerson),
    FOREIGN KEY(IDSupervisor) REFERENCES Physician(IDPhysician)
);

I want to select data from physician and volunteer with the name of each person. Is there any possible way to do this?

Let's say I have this example data:

Person:

IDPerson	Name
P001	Andy
P002	Rudy
P003	Budy
P004	Khal
P005	Apho

Phycisian:

IDPhysician	specialty
P004	heart
P005	brain

Volunteer:

IDVolunteer	IDSupervisor	skill
P001	P004	skill1
P002	P004	skill2
P003	P005	skill2

This is what I expect as an output:

IDVolunteer | Name | IDSupervisor | Name
------------|------|--------------|--------
P001        | Andy | P004         | Khal 
------------|------|--------------|--------
P002        | Rudy | P004         | Khal 
------------|------|--------------|--------
P003        | Budy | P005         | Apho 
------------|------|--------------|--------

CodePudding user response：

Check if this works for you.

SELECT
    V.IDVolunteer,
    P.name,
    V.IDSupervisor,
    Ph.name,
FROM
    Volunteer V
INNER JOIN Person P
    ON P.IDPerson = V.IDVolunteer
INNER JOIN Person Ph
    ON Ph.IDPerson = V.IDSupervisor;