Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h2QDdlI26565 for ; Wed, 26 Mar 2003 07:39:47 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h2QDdjg26557 for ; Wed, 26 Mar 2003 07:39:45 -0600 Received: from fatcity.com (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id CAA88478; Wed, 26 Mar 2003 02:21:10 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00572EDE; Wed, 26 Mar 2003 02:03:41 -0800 Message-ID: Date: Wed, 26 Mar 2003 02:03:41 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: tjambu_fatcity@yahoo.com.au Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: tjambu_fatcity@yahoo.com.au Subject: SQL Problem - Merging Result sets Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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: tjambu_fatcity@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@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).