Re: SQL Syntax Query - sorry

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_at_lycos.co.uk (Colin McGuire) wrote in message news:<ab6cea37.0212141239.2b94bc48_at_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 Sun Dec 15 2002 - 09:01:19 CST