with coupon_data(coupon, value)as  
( 
select 'A',100 from dual union all 
select 'B',40 from dual union all 
select 'C',120 from dual union all 
select 'D',10 from dual union all 
select 'E',200 from dual  
) 
, cap_data(cap_name, cap_limit) as 
( 
select 'Cap1', 150 from dual union all 
select 'Cap2', 70 from dual  
) 
, coupon_cap_mapping(coupon, cap_sequence, cap_name) as 
( 
select 'A',1,'Cap1' from dual union all 
select 'A',2,'Cap2' from dual union all 
select 'B',1,'Cap2' from dual union all 
select 'C',1,'Cap2' from dual union all 
select 'C',2,'Cap1' from dual union all 
select 'D',1,'Cap1' from dual union all 
select 'E',1,'Cap1' from dual union all 
select 'E',2,'Cap2' from dual  
) 
SELECT cd.coupon, 
       cd.value, 
       cap_d.cap_name, 
       ccm.cap_sequence, 
       cap_d.cap_limit 
       --, coupon_usage, cap_remaining  
FROM coupon_data cd 
JOIN coupon_cap_mapping ccm ON ( cd.coupon = ccm.coupon ) 
JOIN cap_data           cap_d ON ( cap_d.cap_name = ccm.cap_name ) 
ORDER BY cd.coupon, 
         ccm.cap_sequence