Re: SQL Syntax Query - sorry

Peter,

Sorry!! Doing to many things at one time and I put sneakers / tennis shoes in mouth.

Your query of:

select familyid from (select distinct familyid, childname from family where childname in ('Robert','Rex')) group by familyid having count(*) = 2;

Does indeed solve the problems, thus far discussed, EVEN having say two "Roberts" with different birthdates in family 17.

( e.g. insert into family values(17,'Robert','17-JAN-47'); )

I realized you had the correct answer while watching a TV show (MSNBC show about prisons - no less) and thought hey, use distinct (or group by) to eliminate the duplicate Roberts. Ah, isn't hat what Pete was doing, I also thought.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote in message news:sObL9.2825$411.1227700344_at_newssvr11.news.prodigy.com...
> 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 - 22:09:15 CST