SQL Syntax Query - sorry

From: Colin McGuire <colinandkaren_at_lycos.co.uk>
Date: 14 Dec 2002 12:39:52 -0800
Message-ID: <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 Received on Sat Dec 14 2002 - 14:39:52 CST