Re: problem with nested selects

I think this solution won't work if there is no message in a forum

(the result should be 0 but the query will give 1 if I do an outer join and will give nothing if I do a join)

Michel Cadot <micadot_at_netcourrier.com> a écrit dans le message : 8io40c$mds$1_at_s2.feed.news.oleane.net...
> select f.fid, f.name, count(*) nbMsg
> from messages m , forums f
> where m.fid = f.fid
> group by f.fid, f.name
> /
>
> --
> Have a nice day
> Michel
>
>
> Olivier Mangez <o.mangez_at_cross-systems.com> a écrit dans le message :
> 961514203.504390_at_sofia.magic.fr...
> > 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
> >
> >
> >
>
>
Received on Tue Jun 20 2000 - 00:00:00 CDT