Re: SQL question

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

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 Mon Sep 17 2001 - 16:13:56 CDT