SQL question

From: Michel R. <mrochon_at_groupehba.com>
Date: Mon, 17 Sep 2001 19:55:24 GMT
Message-ID: <Mysp7.1698$1M2.1015924@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 - 14:55:24 CDT