Re: problem with nested selects

Thanks a lot : it works ;o)

Thomas J. Kyte <tkyte_at_us.oracle.com> a écrit dans le message : 200006202359.SAA26770_at_x55.deja.com...
> In article <961514203.504390_at_sofia.magic.fr>,
> "Olivier Mangez" <o.mangez_at_cross-systems.com> wrote:
> > I have 2 tables :
> >
> > FORUMS contains a primary key FID and another field called NAME
> > MESSAGES contains a foreign key FID that is related to FORUMS
> >
> > How can I get the list of all the forums with the number of messages
 they
> > contain in 1 SQL Query ?
> >
> > (I use Oracle 8)
> >
> > I tried the following query but it doesn't work (but works with
 interbase) :
> >
> > select f.fid, f.name, (select count(*) from messages m where m.fid =
 f.fid)
> > from forums f order by name
> >
> >
>
>
> ops$tkyte_at_8i> create table forums( fid int primary key, name varchar2
> (35) );
>
> Table created.
>
> ops$tkyte_at_8i> create table messages( fid int );
>
> Table created.
>
> ops$tkyte_at_8i>
> ops$tkyte_at_8i> insert into forums values( 1, 'comp.databases.oracle' );
>
> 1 row created.
>
> ops$tkyte_at_8i> insert into forums values(
> 2, 'comp.databases.oracle.server' );
>
> 1 row created.
>
> ops$tkyte_at_8i>
> ops$tkyte_at_8i> insert into messages values ( 1 );
>
> 1 row created.
>
> ops$tkyte_at_8i> insert into messages values ( 1 );
>
> 1 row created.
>
> ops$tkyte_at_8i> insert into messages values ( 1 );
>
> 1 row created.
>
> ops$tkyte_at_8i>
> ops$tkyte_at_8i> select f.fid, f.name, count(m.fid)
> 2 from forums f, messages m
> 3 where f.fid = m.fid(+)
> 4 group by f.fid, f.name
> 5 order by f.name
> 6 /
>
> FID NAME COUNT(M.FID)
> ---------- ------------------------------ ------------
> 1 comp.databases.oracle 3
> 2 comp.databases.oracle.server 0
>
> (outer join to get the 0)
>
> --
> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> Howtos and such: http://osi.oracle.com/~tkyte/index.html
> Oracle Magazine: http://www.oracle.com/oramag
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Wed Jun 21 2000 - 00:00:00 CDT