Tony - 8i does have outer joins, the syntax is (+) - intuitive right? I haven't run this query, but it might give some ideas. Since nobody has responded yet, maybe it'll provoke the experts ;-)
select r1.id, r2.count(*), r3.count(*), r4.count(*) from (select id from master_tab) r1, (select id, count(*) from detail_tab_a group by id) r2, (select id, count(*) from detail_tab_b group by id) r3, (select id, count(*) from detail_tab_c group by id) r4 where r1.id = r2.id (+) r1.id = r3.id (+) r1.id = r4.id (+) -----Original Message----- Sent: Wednesday, March 26, 2003 4:04 AM To: Multiple recipients of list ORACLE-L 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 ------------ ID1, 1 ID2, 3 (R3 = Result of Q3) detail_tab_B ------------ ID2, 7 ID4, 5 ID5, 3 (R4 = Result of Q4) detail_tab_C ------------ ID3, 7 ID5, 1 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
