I have two tables: table_a: table_a (field1 varchar2(10),extra_field varchar2(6)) field1 extra_field '9282013055' '123' '9345015055' '345' '9345015055' '678' table_b: table_b (field2 varchar2(10), field3 varchar2(10), field4 varchar2(10), field5 varchar2(10)) field2 field3 field4 field5 '9282013055' '120/340' '4434' 'David' '9645015111' '120/340' '4434' 'Sami' '9745015999' '120/340' '4434' 'Julia' '9345015055' '140/440' '4424' 'Mary' Note: field3 and field4 in table_b define the rows that correspond to field1 in table_a. I need to create a third table, table_c, where the required output should be: table_c: field2 field5 extra_field '9282013055' 'David' '123' '9645015111' 'Sami' '123' '9745015999' 'Julia' '123' '9345015055' 'Mary' '345' '9345015055' 'Mary' '678' I am trying this statement: create table_c as select field2,field5 from table_b where (field3,field4) in (select field3,field4 from table_b where field2 in (select field1 from table_a) which gets the rows corresponding to field1 in table_a (by using field3 and field4 in table_b). but I need to get extra_field from table_a as well. I am guessing a join should be made between table_a and table_b. Any help appreciated. Any help appreciated.