| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Question
A copy of this was sent to "antonio" <ivkaki_at_hvision.nl>
(if that email address didn't require changing)
On Wed, 3 Feb 1999 17:30:30 +0100, you wrote:
>Hi,
>
>I'm having some difficulties finding out the right SQL statement.
>This is the problem:
>
>I have two tables, let's say table1 and table2.
>
>table1 is the mastertable and table2 is the detail table.
>
>First I have to make a selection in the first table. Then i have to count
>the number of detailrecords.
>I want to display the selected masterrecords and the number of
>detailrecords.
>
>How can I do this?
>
>Antonio.
>
couple of ways. examples assume primary key of MASTER is c1, c2.
select master.c1, master.c2, master.c3, count(*)
from table1 master, table2 detail
where master.c1 = detail.c1 and master.c2 = detail.c2
group master.c1, master.c2, master.c3
/
this works IF you select and group by the PRIMARY KEY and all other columns in master. If you leave out the MASTER PRIMARY KEY, the above will *not* work.
another (probably more expensive) way:
select master.*, detail.cnt
from table1 master,
( select c1, c2, count(*) cnt from table2 group by c1, c2 ) detail where master.c1 = detail.c1 and master.c2 = detail.c2 /
if you have very few rows coming back from master, the following will work as well (don't want to call a pl/sql function in sqlplus thousands of times, so if the query against master returns a couple of records -- this will work fine)
create or replace function detail_cnt( p_c1 in ..., p_c2 in ... ) return number as
l_cnt number;
begin
select count(*) into l_cnt from table2 where c1 = p_c1 and c2 = p_c2;
return l_cnt;
end;
/
select master.*, detail_cnt(c1,c2)
from table1 master
where ......
/
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Feb 03 1999 - 11:21:53 CST
 |
 |