Re: SQL question

select p.no, count(decode(cat,'CHILD',1,0)) CHILD, count(decode(cat,'SPOUSE',1,0)) SPOUSE
 from person p, relation r
 where r.pers_no (+) =p.no
 group by p.no

Regards Christophe Flajolet.

"Michel R." <mrochon_at_groupehba.com> a écrit dans le message news: Mysp7.1698$1M2.1015924_at_carnaval.risq.qc.ca...
> Hello,
>
> I have 2 tables, the 1st one PERSON containing people descriptions and a
2nd
> RELATION containing data about relationships between PERSONs (child,
> spouse,...). I want to write a SQL statement to retrieve the number of
> children and spouses for each person in PERSON. Here's what I have so
far:
>
> select p.no, count(r.pers_no), count(r2.pers_no)
> from person p, relation r, relation r2
> where r.pers_no=p.no and r2.pers_no=p.no and r.cat = 'CHILD' and r2.cat =
> 'SPOUSE'
> group by p.no
>
> This is not working as I would like, so I need a little help.
>
> Thank you all.
>
> MR
>
>
Received on Tue Sep 18 2001 - 02:20:07 CDT