CODEWITHSUNDEEP
sqloracle-database
Chandramohan S
Chandramohan S

Reputation: 45

How to display rows in nested format when matches multiple rows with linking table oracle

Table 1: Income

st_id, st_date,  st_sl, income_amount
-------------------------------------
12   11/11/2018   1    100
12   11/11/2018   2    10
13   11/11/2018   1    50

Table 2: Util

util_date, serial_no, st_id, st_date, st_sl, util_amount
----------------------------------------------------------
12/11/2018     1       12    11/11/2018  1    20
12/11/2018     2       12    11/11/2018  1    50
13/11/2018     1       12    11/11/2018  1    30
12/11/2018     1       13    11/11/2018  1    50

I want the result as:

st_id, st_date,  st_sl, income_amount, util_date, util_amount
---------------------------------------------------------------
12      11/11/2018   1        100        12/11/2018     20
                                         12/11/2018     50
                                         12/11/2018     30
12      11/11/2018   2         10           null       null
13      11/11/2018   1         50        12/11/2018     50

I want a single row from table 1 when the linking table has multiple rows matching. like above.

What could be query to achieve this type of result? Please help.

The below query is normal sql query for linking 2 tables. It gives duplicate like records from Income table (Means single row repeats due to the Income table ref present in two rows in Util table). But I want single row from Income table & multiple rows from Util table like above example.

select st_id, st_date, st_sl, income_amount, util_date, util_amount
  from income i, util u
 where i.st_id = u.st_id
   and i.st_date = u.st_date
   and i.st_sl = u.st_sl;

Upvotes: 1

Views: 71

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

A correct query to show the data you want should be 

select st_id, st_date, st_sl, income_amount, util_date, util_amount
  from income i 
  left join util u on 
         i.st_id = u.st_id
     and i.st_date = u.st_date
     and i.st_sl = u.st_sl;

Please note the left in the join condition.

But you want that "duplicate" income rows to be suppressed to a single row. This can be done in multiple ways but one ideea would be:

select case when util.serial_no = 1 then i.st_id end as st_id, 
       case when util.serial_no = 1 then i.st_date end as st_date, 
       case when util.serial_no = 1 then i.st_sl end as st_sl, 
       income_amount, 
       util_date, 
       util_amount
  from income i 
  left join util u on 
         i.st_id = u.st_id
     and i.st_date = u.st_date
     and i.st_sl = u.st_sl;

UPDATE1:

As the sample data and explanation changed, another query to comply with the request would be:

select 
  case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1 
     then i.st_id 
  end as st_id, 
  case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1 
     then i.st_date 
  end as st_date, 
  case when row_number() over (partition by i.st_id, i.st_sl, u.util_date order by u.serial_no) = 1 
     then i.st_sl 
  end as st_sl, 
  i.income_amount, 
  u.util_date, 
  u.serial_no,
  u.util_amount
from income i 
left join util u on 
         i.st_id = u.st_id
     and i.st_date = u.st_date
     and i.st_sl = u.st_sl;

So, the first three columns get values only for the first rows in the group of std_id, st_sl, util_date.

See a fiddle for demonstration. (also it would be really nice if you give a fiddle with test data in the question next time.)

Upvotes: 1

Related Questions