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

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

RE: SQL Problem - Merging Result sets

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 26 Mar 2003 09:34:01 -0800
Message-ID: <F001.00573661.20030326093401@fatcity.com>


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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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 - 11:34:01 CST

Original text of this message

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