CODEWITHSUNDEEP
oracle-database
mukul
mukul

Reputation: 5

oracle left outer joins not showing null values but displays same value

Problem is in left outer join, when there are no rows in right side table then it does not display null values, it displays previous values....

Like this....

1 st Table contains

PGMTX_CODE    PGMTX_MARKS    PGMTX_TOTQSTN
-------------------------------------------
EE            1              5

EE            2              5

EE            3              0

EE            4              0

2 nd Table contains

PGMTX_CODE    PGMTX_MARKS    PGMTX_ACTUSEDQST
-------------------------------------------
EE            1              5

So I want result like...

PGMTX_MARKS    PGMTX_TOTQSTN     PGMTX_ACTUSEDQST
--------------------------------------------------
1              5                 5

2              5                 blank

3              0                 blank

4              0                 blank

I use query like this...

SELECT m.PGMTX_MARKS, 
       m.PGMTX_TOTQSTN, 
       tlm.PGMTX_ACTUSEDQST, 
  from PAPERGEN_MTL_OEX m 
       left OUTER JOIN  PAPERGEN_TLMTL_OEX tlm 
         ON m.PGMTX_CODE=tlm.PGMTX_CODE
 where m.PGMTX_CODE='EE' 
 order by m.PGMTX_MARKS

But I got result like

PGMTX_MARKS    PGMTX_TOTQSTN     PGMTX_ACTUSEDQST
--------------------------------------------------
1              5                 5

2              5                 5

3              0                 5

4              0                 5

Upvotes: 0

Views: 119

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Your join condition is wrong, should be

ON m.PGMTX_CODE=tlm.PGMTX_CODE AND m.PGMTX_MARKS = tlm.PGMTX_MARKS

Upvotes: 3

Related Questions