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?
Advertisement
Answer
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';