Re: SQL Syntax Query - sorry

Peter,

One problem is if a family has two Roberts and no Rex. I wonder if "FamilyID" and "Childname" can be a composite Primary Key?

Colin?!

Will "FamilyID" and "Childname" be unique (e.g. able to be a composite Primary Key?)?

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Peter J. Holzer" <hjp-usenet_at_hjp.at> wrote in message news:slrnavpi7k.kq1.hjp-usenet_at_teal.hjp.at...
> On 2002-12-15 13:24, Colin McGuire <colinandkaren_at_lycos.co.uk> wrote:
> > Bertram, thank you for thinking about this problem. I think what you
> > are proposing is something like the following structure (create and
> > insert into table below).
> >
> [test data omitted]
> >
> > I am wanting to retrieve just familyid 2 and 7 since these are the
> > only two families that have at least two children such that two of the
> > childrens names are 'Rex' and 'Robert'. The query your propose
> > unfortunately also retrieves familyid 17, which doesn't meet this
> > criteria.
> >
> >
> >
> > SQL>
> > SQL> select familyid
> > 2 from family
> > 3 where familyid in (select familyid
> > 4 from family
> > 5 group by familyid
> > 6 having count(*) >= 2)
> > 7 and childname in ('Robert','Rex');
>
> The queries are reversed: You have to filter for the children's names
> first and then count the results:
>
> SQL> edit
> Wrote file afiedt.buf
>
> 1 select familyid from (
> 2 select distinct familyid, childname from family
> 3 where childname in ('Robert','Rex')
> 4 )
> 5 group by familyid
> 6* having count(*) = 2
> SQL> /
>
> FAMILYID
> ----------
> 2
> 7
>
> (The distinct is to ignore multiple children with the same name in the
> family - if that isn't possible, remove it and get rid of one sort)
>
> hp
>
> --
> _ | Peter J. Holzer | Schlagfertigkeit ist das, was einem
> |_|_) | Sysadmin WSR | auf dem Nachhauseweg einfällt.
> | | | hjp_at_hjp.at | -- Lars 'Cebewee' Noschinski in dasr.
> __/ | http://www.hjp.at/ |
Received on Sun Dec 15 2002 - 21:31:36 CST