Sql Query Problem

Question

i have problem when joining tables(left join) table1:

id1  amt1
1    100
2    200
3    300

table2:

id2  amt2
1    150
2    250
2    350

my Query: select id1,amt1,id2,amt2 from table1 left join table2 on table2.id1=table1.id2

my supposed o/p is:

      id1 amt1  id2 amt2
row1: 1   100   1    150
row2: 2   200   2    250
row3: 2   200   2    350

i want o/p in row3 as

2 null 2 350

ie i want avoid repetetion of data(amt1) friends help!

Answer 1

select distinct id1,amt1,id2,amt2 from table1 left join table2 on table2.id1=table1.id2

try this ?

Answer 2

Using LEAD and LAG gives acces to previous or following rows in oracle.

SELECT id1, decode(amt1, lag(amt1) over (order by id1, id2), '', amt1) amt1, 
       id2, amt2 
FROM table1 left join table2 on table2.id1=table1.id2
ORDER BY id1, id2

The order of the query and the order given to the lag function should be the same.

Explanation:
If the current am1 is the same as the preceding amt1 (preceding in the given order) then omit the value.

EDIT According to your comment, add an additional check for id changes.

SELECT id1, 
       decode(id1, lag(id1) over (order by id1, id2), 
           decode(amt1, lag(amt1) over (order by id1, id2), '', amt1), 
           amt1) amt1, 
       id2, amt2 
FROM table1 left join table2 on table2.id1=table1.id2
ORDER BY id1, id2

Use the same LAG feature to check for id changes. The expression is a bit more complex, but its comparable with a nested if statement.