Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> SQL Problem - Merging Result sets

SQL Problem - Merging Result sets

From: <tjambu_fatcity_at_yahoo.com.au>
Date: Wed, 26 Mar 2003 02:03:41 -0800
Message-ID: <F001.00572EDE.20030326020341@fatcity.com>


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_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-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).
Received on Wed Mar 26 2003 - 04:03:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US