How to add two result sets.

[kaviyarasan]

hi,

I have three tables named WEB, WEB_H, WEB_S. Fields in WEB are id and some other. Fields in WEB_H are id, h_id and some other. Fields in WEB_S are id, s_id and some other. In the three tables, id represent a same entity. WEB table is base table. For an id in WEB table, the matched datas may or may not be present in both, table WEB_S and WEB_H. But it is sure that atleast either of the two tables web_s or web_h has matched datas for id of WEB. What I tried to do is,

For id 14695521 from WEB, I get the data from WEB_H using left outer join and got the result as

14695521 23857780
14695521 23857781

For the same id from WEB, I get the data from WEB_S using left outer join and got the result as

14695521 38562531
14695521 38562532
14695521 38562533

I want to simply combine the result as shown below

(Manually typed)

14695521 23857780 38562531
14695521 23857781 38562532
14695521 (null) 38562533

I tried the below two queries:

1. select distinct * from
(
select
c.id,c.h_id,d.s_id from
(select distinct * from
(select
a.id id, b.h_id h_id
from
web a left join web_h b on a.id=b.id
where a.id='14695521')) c left join web_s d on c.id=d.id
)


2. select distinct c.id, c.h_id, d.s_id from
(
select distinct a.id id, b.h_id h_id from
web a,
web_h b
where aid=b.id(+)
and a.id='14695521'
) c, web_s d
where c.id= d.id(+)


But both the queries return the result as below

14695521 23857781 38562531
14695521 23857781 38562532
14695521 23857780 38562532
14695521 23857781 38562533
14695521 23857780 38562531
14695521 23857780 38562533


Please help me to get the result as I mentioned earlier.

[kaviyarasan]

If you have any logic also, reply with that.

[amitpatel66]

try this query:

 
SQL> SELECT NVL(x.id,y.id),x.hid,y.lid FROM 
  2            (select distinct x.id,hid,row_number() over(partition by x.id order by x.id,hid) rn  
FROM 
  3             www x, wwwh h 
  4             where x.id = h.id) x, 
  5             (SELECT distinct x.id,lid,row_number() over(partition by x.id order by x.id,lid) r_n 
  6             from www x, wwwl l 
  7             where x.id = l.id) y 
  8             WHERE x.id(+) = y.id 
  9             AND x.rn(+) = y.r_n; 
 
NVL(X.ID,Y.ID)        HID        LID
-------------- ---------- ----------
      14695521   23857780   38562531
      14695521   23857781   38562532
      14695521              38562533
 
SQL> 
 
 

I have used below table names instead of your actuals:

web -> www
web_h -> wwwh
web_s -> wwwl

[kaviyarasan]

Amazing amit..... very much thankyou... If possible prefer me some ways to excel in ORACLE....

Thanks for your great help.

[kaviyarasan]

Got a problem buddy.....

We dont know whether WEB_H or WEB_S will have more datas for the id of WEB. When the above code is executed, it works well for the id which has more number of datas in WEB_S. That is, im getting the actual result for the scenario i explained above..

If WEB_H has more datas.
For ex. Im changing the above scenario.

For id 14690001 from WEB, I get the data from WEB_H using left outer join and got the result as

14690001 38562531
14690001 38562532
14690001 38562533

For the same id from WEB, I get the data from WEB_S using left outer join and got the result as

14690001 23857780
14690001 23857781

If I execute the above code for this scenario, Im getting the result as

14690001 38562531 23857780
14690001 38562532 23857781..

As you see, the last data of WEB_H is omitted...

Please advice

[amitpatel66]

You will need to do the LEFT and RIGHT OUTER JOIN and UNION SET will take care of the duplicates. FULL OUTER JOIN will not work as expected.

Try this:

SELECT NVL(x.id,y.id),x.hid,y.lid FROM  
              (select distinct x.id,hid,row_number() over(partition by x.id order by x.id,hid) rn   
FROM  
               www x, wwwh h  
               where x.id = h.id) x RIGHT OUTER JOIN
               (SELECT distinct x.id,lid,row_number() over(partition by x.id order by x.id,lid) r_n  
               from www x, wwwl l  
               where x.id = l.id) y  
               ON x.id = y.id  
               AND x.rn = y.r_n
UNION
SELECT NVL(x.id,y.id),x.hid,y.lid FROM  
              (select distinct x.id,hid,row_number() over(partition by x.id order by x.id,hid) rn   
FROM  
               www x, wwwh h  
               where x.id = h.id) x LEFT OUTER JOIN
               (SELECT distinct x.id,lid,row_number() over(partition by x.id order by x.id,lid) r_n  
               from www x, wwwl l  
               where x.id = l.id) y  
               ON x.id = y.id  
               AND x.rn = y.r_n