Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Limit number of rows fetching in a left outer join in Oracle

User_PBFCFOct 30 2022

I'm going to create a data model in Oracle fusion applications. I need to create column 'End_date' base on two tables in the query. So I used two methods.
Using a subquery:

SELECT *
  FROM (SELECT projects_A.end_date
          FROM projects_A, projects_B
         WHERE projects_A.p_id = projects_B.p_id
           AND rownum = 1)

Using a LEFT OUTER JOIN:

SELECT projects_A.end_date
  FROM projects_A
  LEFT JOIN projects_B
    ON projects_A.p_id = projects_B.p_id
 WHERE rownum = 1

Here when I used a subquery, the query returns the results as expected. But when I use left outer join with ''WHERE rownum=1" the result is Zero. Without "WHERE rownum = 1" it retrieves all the results. But I want only the first result. So how can I do that using left outer join? Thank you.

Comments
Post Details
Added on Oct 30 2022
1 comment
2,346 views