Colin, I do not know whether my solution will do a full table scan or
not, and therefore you might not want to consider this approach
because it is inefficient. Someone with more knowledge should comment.

I put forward the following SQL query (following on from Bert-Bear's
table structure).

SELECT familyid
FROM family
GROUP BY familyid
HAVING COUNT(CASE WHEN childname IN('Robert','Rex') THEN 1 ELSE NULL
END)=2;

Of course it might fail to give a valid resultset should a family call
their children the same name, ie count two Roberts !

Tony


colinandkaren@lycos.co.uk (Colin McGuire) wrote in message news:<ab6cea37.0212141239.2b94bc48@posting.google. com>...
> Embarassingly I am in the position of having to publically ask a
> question on syntax for an SQL query. I would like an SQL query that
> retrieves the family ID for all families that have at least two
> children such that two of the childrens names are 'Rex' and 'Robert'.
>
> I have created some dummy data/table definitions below. In addition,
> at the bottom of this posting, you can find my sad yet successful
> attempt at constructing such a query (using Oracle 8 personal on a
> Windows XP home computer).
>
>
> drop table tbl_family;
> drop type children_nt;
> drop type children_ty;
>
> create or replace type children_ty as object (childname varchar2(25),
> birthdate date);
> /
> create or replace type children_nt as table of children_ty;
> /
>
> --create a table with nested table of the children
> create table tbl_family(familyid number(10) primary key, children
> children_nt)
> nested table children store as tbl_nt_children;
>
> --insert some dummy data
> insert into tbl_family(familyid, children)
> values (1,children_nt(children_ty('Robert','01-Jul-75')));
>
> insert into tbl_family(familyid, children)
> values (2,children_nt(children_ty('Mary','21-Aug-70'),
> children_ty('Felix','21-Sep-72'),
> children_ty('Robert','14-Jan-68'),
> children_ty('Rex','17-Feb-66')));
>
> insert into tbl_family(familyid, children)
> values (5,children_nt(children_ty('Douglas','21-Aug-70'),
> children_ty('William','04-Apr-73')));
>
> insert into tbl_family(familyid, children)
> values (9,children_nt(children_ty('Miranda','28-Apr-69'),
> children_ty('Marion','28-Apr-69')));
>
> insert into tbl_family(familyid, children)
> values (7,children_nt(children_ty('Murtle','02-Mar-63'),
> children_ty('Robert','25-Feb-58'),
> children_ty('Rex','31-Jan-61')));
>
> --do a quick dump of everything so I can see what is going on
> select t1.familyid,t2.*
> from tbl_family t1, table(t1.children) t2;
>
> --and the following query gives on solution to my question 'what are
> the
> --id's of the families that have at least two children and two of the
> --childrens names are Rex and Robert.
> select t1.familyid
> from tbl_family t1,
> table(t1.children) t2,
> tbl_family t3,
> table(t3.children) t4
> where t2.childname = 'Rex' and t4.childname='Robert'
> and t1.familyid=t3.familyid;
>
> I don't like this query at all because for two childrens names, I
> effectively have four tables/inner joins etc, and if I were to want to
> ask for names of families that have 8 children with various names (yes
> I know this is unlikely but the real data isn't families, just using
> this model so that everyone can easily understand my requirements),
> then the whole construction loses all scalability. What I would like
> is for one of the Oracle guru's to point me in the right direction and
> write something magical and syntactically correct if possible (unlike
> the following contrived and invalid pseudo-query that represents what
> I want):
>
> select familyid
> from .......
> where all the childrensnames are in ('Rex' and 'Robert');
>
> All comments most welcome, even ones criticising my design
> Kind regards
>
> Colin McGuire

Tony,

One problem is if a familyID has two Roberts (and no Rex).

I wondering if FamilyID and Childname are unique? (I know you don't know.)

Colin? Are FamilyID and Childname unique are non-unique?

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram


"Tony Cantara" <tonycantara123@hotmail.com> wrote in message
news:3d0c2563.0212150701.2a037488@posting.google.c om...
> Colin, I do not know whether my solution will do a full table scan or
> not, and therefore you might not want to consider this approach
> because it is inefficient. Someone with more knowledge should comment.
>
> I put forward the following SQL query (following on from Bert-Bear's
> table structure).
>
> SELECT familyid
> FROM family
> GROUP BY familyid
> HAVING COUNT(CASE WHEN childname IN('Robert','Rex') THEN 1 ELSE NULL
> END)=2;
>
> Of course it might fail to give a valid resultset should a family call
> their children the same name, ie count two Roberts !
>
> Tony
>
>
> colinandkaren@lycos.co.uk (Colin McGuire) wrote in message
news:<ab6cea37.0212141239.2b94bc48@posting.google. com>...
> > Embarassingly I am in the position of having to publically ask a
> > question on syntax for an SQL query. I would like an SQL query that
> > retrieves the family ID for all families that have at least two
> > children such that two of the childrens names are 'Rex' and 'Robert'.
> >
> > I have created some dummy data/table definitions below. In addition,
> > at the bottom of this posting, you can find my sad yet successful
> > attempt at constructing such a query (using Oracle 8 personal on a
> > Windows XP home computer).
> >
> >
> > drop table tbl_family;
> > drop type children_nt;
> > drop type children_ty;
> >
> > create or replace type children_ty as object (childname varchar2(25),
> > birthdate date);
> > /
> > create or replace type children_nt as table of children_ty;
> > /
> >
> > --create a table with nested table of the children
> > create table tbl_family(familyid number(10) primary key, children
> > children_nt)
> > nested table children store as tbl_nt_children;
> >
> > --insert some dummy data
> > insert into tbl_family(familyid, children)
> > values (1,children_nt(children_ty('Robert','01-Jul-75')));
> >
> > insert into tbl_family(familyid, children)
> > values (2,children_nt(children_ty('Mary','21-Aug-70'),
> > children_ty('Felix','21-Sep-72'),
> > children_ty('Robert','14-Jan-68'),
> > children_ty('Rex','17-Feb-66')));
> >
> > insert into tbl_family(familyid, children)
> > values (5,children_nt(children_ty('Douglas','21-Aug-70'),
> > children_ty('William','04-Apr-73')));
> >
> > insert into tbl_family(familyid, children)
> > values (9,children_nt(children_ty('Miranda','28-Apr-69'),
> > children_ty('Marion','28-Apr-69')));
> >
> > insert into tbl_family(familyid, children)
> > values (7,children_nt(children_ty('Murtle','02-Mar-63'),
> > children_ty('Robert','25-Feb-58'),
> > children_ty('Rex','31-Jan-61')));
> >
> > --do a quick dump of everything so I can see what is going on
> > select t1.familyid,t2.*
> > from tbl_family t1, table(t1.children) t2;
> >
> > --and the following query gives on solution to my question 'what are
> > the
> > --id's of the families that have at least two children and two of the
> > --childrens names are Rex and Robert.
> > select t1.familyid
> > from tbl_family t1,
> > table(t1.children) t2,
> > tbl_family t3,
> > table(t3.children) t4
> > where t2.childname = 'Rex' and t4.childname='Robert'
> > and t1.familyid=t3.familyid;
> >
> > I don't like this query at all because for two childrens names, I
> > effectively have four tables/inner joins etc, and if I were to want to
> > ask for names of families that have 8 children with various names (yes
> > I know this is unlikely but the real data isn't families, just using
> > this model so that everyone can easily understand my requirements),
> > then the whole construction loses all scalability. What I would like
> > is for one of the Oracle guru's to point me in the right direction and
> > write something magical and syntactically correct if possible (unlike
> > the following contrived and invalid pseudo-query that represents what
> > I want):
> >
> > select familyid
> > from .......
> > where all the childrensnames are in ('Rex' and 'Robert');
> >
> > All comments most welcome, even ones criticising my design
> > Kind regards
> >
> > Colin McGuire

Tony,

I think Peter got the solution in a different thread. What do you think of:

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

as being the solution?

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram


"Tony Cantara" <tonycantara123@hotmail.com> wrote in message
news:3d0c2563.0212150701.2a037488@posting.google.c om...
> Colin, I do not know whether my solution will do a full table scan or
> not, and therefore you might not want to consider this approach
> because it is inefficient. Someone with more knowledge should comment.
>
> I put forward the following SQL query (following on from Bert-Bear's
> table structure).
>
> SELECT familyid
> FROM family
> GROUP BY familyid
> HAVING COUNT(CASE WHEN childname IN('Robert','Rex') THEN 1 ELSE NULL
> END)=2;
>
> Of course it might fail to give a valid resultset should a family call
> their children the same name, ie count two Roberts !
>
> Tony
>
>
> colinandkaren@lycos.co.uk (Colin McGuire) wrote in message
news:<ab6cea37.0212141239.2b94bc48@posting.google. com>...
> > Embarassingly I am in the position of having to publically ask a
> > question on syntax for an SQL query. I would like an SQL query that
> > retrieves the family ID for all families that have at least two
> > children such that two of the childrens names are 'Rex' and 'Robert'.
> >
> > I have created some dummy data/table definitions below. In addition,
> > at the bottom of this posting, you can find my sad yet successful
> > attempt at constructing such a query (using Oracle 8 personal on a
> > Windows XP home computer).
> >
> >
> > drop table tbl_family;
> > drop type children_nt;
> > drop type children_ty;
> >
> > create or replace type children_ty as object (childname varchar2(25),
> > birthdate date);
> > /
> > create or replace type children_nt as table of children_ty;
> > /
> >
> > --create a table with nested table of the children
> > create table tbl_family(familyid number(10) primary key, children
> > children_nt)
> > nested table children store as tbl_nt_children;
> >
> > --insert some dummy data
> > insert into tbl_family(familyid, children)
> > values (1,children_nt(children_ty('Robert','01-Jul-75')));
> >
> > insert into tbl_family(familyid, children)
> > values (2,children_nt(children_ty('Mary','21-Aug-70'),
> > children_ty('Felix','21-Sep-72'),
> > children_ty('Robert','14-Jan-68'),
> > children_ty('Rex','17-Feb-66')));
> >
> > insert into tbl_family(familyid, children)
> > values (5,children_nt(children_ty('Douglas','21-Aug-70'),
> > children_ty('William','04-Apr-73')));
> >
> > insert into tbl_family(familyid, children)
> > values (9,children_nt(children_ty('Miranda','28-Apr-69'),
> > children_ty('Marion','28-Apr-69')));
> >
> > insert into tbl_family(familyid, children)
> > values (7,children_nt(children_ty('Murtle','02-Mar-63'),
> > children_ty('Robert','25-Feb-58'),
> > children_ty('Rex','31-Jan-61')));
> >
> > --do a quick dump of everything so I can see what is going on
> > select t1.familyid,t2.*
> > from tbl_family t1, table(t1.children) t2;
> >
> > --and the following query gives on solution to my question 'what are
> > the
> > --id's of the families that have at least two children and two of the
> > --childrens names are Rex and Robert.
> > select t1.familyid
> > from tbl_family t1,
> > table(t1.children) t2,
> > tbl_family t3,
> > table(t3.children) t4
> > where t2.childname = 'Rex' and t4.childname='Robert'
> > and t1.familyid=t3.familyid;
> >
> > I don't like this query at all because for two childrens names, I
> > effectively have four tables/inner joins etc, and if I were to want to
> > ask for names of families that have 8 children with various names (yes
> > I know this is unlikely but the real data isn't families, just using
> > this model so that everyone can easily understand my requirements),
> > then the whole construction loses all scalability. What I would like
> > is for one of the Oracle guru's to point me in the right direction and
> > write something magical and syntactically correct if possible (unlike
> > the following contrived and invalid pseudo-query that represents what
> > I want):
> >
> > select familyid
> > from .......
> > where all the childrensnames are in ('Rex' and 'Robert');
> >
> > All comments most welcome, even ones criticising my design
> > Kind regards
> >
> > Colin McGuire