RE: SQL Problem - Merging Result sets
Tony, this works also: select distinct r1.id, count(r2.id),count(r3.id),count(r4.id) from r1, r2, r3, r4 where r2.id (+) = r1.id and r3.id (+) = r1.id and r4.id (+) = r1.id group by r1.id Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, March 26, 2003 11:09 AM To: Multiple recipients of list ORACLE-L 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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).
RE: SQL Problem - Merging Result sets
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).
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 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: Jeroen van Sluisdam 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).
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 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).
