Home > Enterprise >  How to select a same column from a specialized table in PostgreSQL?
How to select a same column from a specialized table in PostgreSQL?

Time:09-16

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;
  • Related