Re: problem with nested selects

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


Sent via Deja.com http://www.deja.com/
Before you buy.