Re: SQL Syntax Query - sorry

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_at_hotmail.com> wrote in message news:3d0c2563.0212150701.2a037488_at_posting.google.com...
> 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 - 22:13:01 CST