CODEWITHSUNDEEP
oracle-database
user1502952
user1502952

Reputation: 1420

Fetching other rows of table when performing inner join over multiple fields (oracle query)

select pmt.col1,table2.col1,table2.col3,table3.col1,table3.col1 
from   table2 inner join (select distinct 
col1,col2 from table1) pmt on 
table2.col1=pmt.col1 inner join table3 on 
table3.col1=table1.col2 where table2.col2 is null;

Is there any way I can select pmt.col3(which is other column of table1) in this very query only.

Thanks very much

Upvotes: 2

Views: 40

Answers (1)

Trinimon
Trinimon

Reputation: 13967

Simply select the column in a the sub query. Use for instance max for limiting the result set to one record:

select pmt.col1,
       (select max(col3) 
          from table1 t1
         where t1.col1 = pmt.col1
           and t1.col2 = pmt.col2) col3,
       table2.col1,
       table2.col3,
       table3.col1,
       table3.col1 
  from table2 
 inner join (select distinct col1,col2
               from table1) pmt 
    on table2.col1=pmt.col1 
 inner join table3 
    on table3.col1=table1.col2 
 where table2.col2 is null;

Upvotes: 2

Related Questions