Inputting data into a table from two different tables-CodePudding

I have these 3 tables with data

SQL> select * from subject; 

 SUBJECTID LNAME    FNAME      PROJID
---------- ------------ ---------- ----------
     10011 Indy     Eva        XYZ01
     20022 Jordan   Sam        XYZ01
     30033 Jordan   Mary       XYZ01
     40044 Belmont  Renee      XYZ02
     50055 Pissaro  Becky      XYZ02
     60066 Nadal    Becky      XYZ03
     70077 Bardot   Brigitte   XYZ03
     80088 null     Eva        XYZ03
     90099 Garnet   Larry      XYZ04
     10111 Isner    Monica     XYZ04
     11011 Dupont   Marty      XYZ05

11 rows selected.

SQL> select * from project;

PROJID     MEDICNAME  PURPOSE      START_DATE  END_DATE     PI_ID
---------- ---------- ------------ ----------- ----------- ----------
XYZ02      Medic1     diabetes  01-oct-2018   31-jul-2022       10001
XYZ01      Medic1     foot      01-sep-2019   31-jul-2021       10001
XYZ04      Medic3     spleen    10-jan-2019   31-jul-2021       10001
XYZ05      Medic5     spleen    10-jul-2020    1-jan-2021       10002
XYZ03      Medic3     lung      01-nov-2016   31-dec-2022       10002

SQL> select * from researcher;

       PID LNAME    FNAME
---------- ------------ ----------
     10001 Elgar    Dawn
     10002 Jordan   Daniel
     10003 Jordan   Athena
     10004 Rivers   Karen
     10005 Gomez    Tracy
     10006 Gomez    Jenny
     10007 Perry    Eva
     10008 McHale   Vicky

8 rows selected.

and then created a third table that looks like this

SQL> CREATE TABLE n_subject
  2  (SubjID number(7),
  3  Lastname varchar2(12),
  4  Firstname varchar2(10));

I want to populate my new table with the Subjects who were involved in projects that were lead by Dawn Elgar (PID is 10001). Is there a way to do that across 3 tables? I am close with code that looks like this

SQL> insert into n_subject (subjid, lastname, firstname)
  2  select subjectid, lname, fname
  3  from subject where projid = 'XYZ01' or projid = 'XYZ02' or projid = 'XYZ04';

but am trying to get the data in there across all the three tables instead, using the ProjectId and the PID. Is this possible?

CodePudding user response：

you can use select with inner join statement

INSERT INTO n_subject
        (subjid, lastname, firstname)
SELECT subjectid,
       lname,
       fname
FROM   [subject]
       JOIN [project]
       ON [project].projid = [subject].projid
WHERE  [project].pi_id = '10001';