Re: problem with nested selects

From: <tandym_at_hotmail.com>
Date: 2000/06/20
Message-ID: <8iohcr$oej$1@nnrp1.deja.com>#1/1

try this...

  1 select a.name, count(b.fid)
  2 from forums a, messages b
  3 where a.fid = b.fid
  4* group by a.name
SQL> / NAME COUNT(B.FID)
---------- ------------

DIETS                 4
Houses                2
Oracle                1
Writing               3

hth
sonya

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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jun 20 2000 - 00:00:00 CDT