Re: SQL question

> Remove the outer join (+) if you want records/persons with at least one
> child or spouse

It isn't mandatory, because in this case the SUM is equal to zero

"Anurag Varma" <avdbi_at_nospam.hotmail.com> a écrit dans le message de news: oItp7.5375$2n.475532_at_news02.optonline.net...
>
> SELECT p.no, SUM(DECODE(r.cat, 'CHILD', 1, 0)) nbr_child,
> SUM(DECODE(r.cat, 'SPOUSE', 1, 0)) nbr_spouse
> FROM person p, relation r
> WHERE p.no = r.pers_no(+)
> GROUP BY p.no
> /
>
> :) Behold the power of "decode"
> Anurag
>
> "Michel R." <mrochon_at_groupehba.com> wrote in 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 - 09:01:41 CDT