| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Problem - Merging Result sets
Hi,
Try something like
select id, numberrowsA, numberrowsB ..
from master_tab m,
(select id, count(*) "numberrowsa"
from detail_tabA a
where m.id = a.id
group by a.id) da,
(select id, count(*) "numberrowsb"
from detail_tabb b
where m.id = b.id
group by b.id) db,
.......
where
da.id = m.id
and db.id = m.id
Hth,
Jeroen
-----Oorspronkelijk bericht-----
Van: tjambu_fatcity_at_yahoo.com.au [mailto:tjambu_fatcity_at_yahoo.com.au]
Verzonden: woensdag 26 maart 2003 11:04
Aan: Multiple recipients of list ORACLE-L
Onderwerp: SQL Problem - Merging Result sets
Hi all
I have a question which is probably easy for some of you but it is 21:00 and my mind is not working. Would appreciate it if someone can point me in the right direction. version is 8i so no ANSI OUTER JOIN.
I have 4 tables:
master_tab, ID is PK detail_tab_A, ID is FK detail_tab_B , ID is FK detail_tab_C , ID is FK
For each row in the master_tab I would like to know the number of rows in the corresponding child tables. Can this be done in a single DML statement?
My base queries are:
Q1= select id,.... from master_tab; Q2= Select id,count(*) from detail_tab_A group by id; Q3= Select id,count(*) from detail_tab_B group by id; Q4= Select id,count(*) from detail_tab_C group by id;
Result set
(R1 = Result of Q1) Master_tab
ID1,... ID2,... ID3,... ID4,... ID5,...
(R2 = Result of Q2) detail_tab_A
(R3 = Result of Q3) detail_tab_B
ID2, 7 ID4, 5 ID5, 3
(R4 = Result of Q4) detail_tab_C
The result to look something like
(R1+ R2+ R3+ R4)
ID1,..., 1, -, - ID2,..., 3, 7, - ID3,..., -, -, 7 ID4,..., -, 5, - ID5,..., -, 3, 1
where - mean blank or 0.
Any pointers would be a great help.
ta
tony
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: tjambu_fatcity_at_yahoo.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Wed Mar 26 2003 - 04:53:38 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeroen van Sluisdam INET: jeroen.van.sluisdam_at_vrijuit.nl Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |