I've already search the net about this but it can't solve my problem.

I have two tables one with 10 rows and other with 9 rows

table 1

---------- ---------- 
1          AC_ACCOUNT 
2          AC_MGR 
3          AD_ASST 
4          AD_PRES 
5          AD_VP 
6          FI_ACCOUNT 
7          FI_MGR 
8          HR_REP 
9          IT_PROG 
10         MK_MAN 

table 2

RNUM       JOB_ID 
---------- ---------- 
1          AC_ACCOUNT 
2          AC_MGR 
3          AD_ASST 
4          AD_PRES 
5          AD_VP 
6          FI_ACCOUNT 
7          FI_MGR 
8          HR_REP 
9          IT_PROG 

I want to join these two tables but I want the output to be like this

RNUM       JOB_ID            RNUM       JOB_ID 
---------- ----------        ---------- ---------- 
1          AC_ACCOUNT        null       null 
2          AC_MGR            1          AC_ACCOUNT 
3          AD_ASST           2          AC_MGR 
4          AD_PRES           3          AD_ASST 
5          AD_VP             4          AD_PRES 
6          FI_ACCOUNT        5          AD_VP 
7          FI_MGR            6          FI_ACCOUNT 
8          HR_REP            7          FI_MGR 
9          IT_PROG           8          HR_REP 
10         MK_MAN            9          IT_PROG 

my first code is

select a.*, b.* 
from samp a, samp2 b where a.rnum = b.rnum(+) 

but the result of my code is

RNUM       JOB_ID                RNUM       JOB_ID
---------- ----------            ---------- ---------- 
1          AC_ACCOUNT            1          AC_ACCOUNT 
2          AC_MGR                2          AC_MGR 
3          AD_ASST               3          AD_ASST 
4          AD_PRES               4          AD_PRES 
5          AD_VP                 5          AD_VP 
6          FI_ACCOUNT            6          FI_ACCOUNT 
7          FI_MGR                7          FI_MGR 
8          HR_REP                8          HR_REP 
9          IT_PROG               9          IT_PROG 
10         MK_MAN                null       null 

I want to connect table 2's number 1 entry(AC_ACCOUNT) to table 1's number 2 entry(AC_MGR) as what you can see in my desired output.

I used rownum as a technique just to have a connection between my two tables.

I think I misunderstood left outer join here, please kindly enlighten me how to achieve my desired output.

  • 1
    I just want to connect the first entry of my second table to the 2nd entry of my 1st table leaving the first with null value. I just want to explore sql more and the capabilities of joins. The tables were just for example – Blank Aug 14 '17 at 10:00

When joining you join one key to another x=y but you seem to want to join x=y-1 instead. In your case you want to join 1 with 0 and 2 with 1. This is not a good practice. When joining you always you should join keys with eachother without chaning them.

You should think about what you really want as a result because it is not a logical join to do. But to genereate the result you want you can do like this:

select a.*, b.* from t1 a LEFT OUTER JOIN t2 b on a.rnum-1 = b.rnum

DB Fiddle

This is a good reference image for future use: enter image description here

  • 2
    Why not logical thing to do? I've joined tables using calculations like this before. It comes up occasionally in business requirements, such as needing to do calculations on successive events. – Colin 't Hart Aug 14 '17 at 7:00
  • 1
    @Colin'tHart what if one or multiple events is removed then this kind of calculation will be messed up when joining on a key calculation. Instead you could use LAG() and LEAD() to get the previous or next rows values. You need to have a really specific need to do it this way. Im not saying its wrong just that you should think before doing it. – Daniel Björk Aug 14 '17 at 7:19

Maybe you want to use hierarchical query with connect by clause instead of doing something like +1

Here is documentation: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm


I think I find the right answer now:

SELECT a.*, b.* 
FROM samp a 
LEFT JOIN samp2 b 
ON b.rnum + 1 = a.rnum 
ORDER BY a.rnum;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.