Re: SQL Syntax Query - sorry

"Colin McGuire" <colinandkaren_at_lycos.co.uk> wrote in message news:ab6cea37.0212150524.c5e8b5d_at_posting.google.com...
> 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).
>
> drop table family;
> create table family(familyid number(10), childname varchar2(25),
> birthdate date);
>
> insert into family(familyid,childname,birthdate)
> values(2,'Mary','21-Aug-70');
> insert into family(familyid,childname,birthdate)
> values(2,'Felix','21-Sep-72');
> insert into family(familyid,childname,birthdate)
> values(2,'Robert','14-Jan-68');
> insert into family(familyid,childname,birthdate)
> values(2,'Rex','17-Feb-66');
>
> insert into family(familyid,childname,birthdate)
> values(5,'Douglas','21-Aug-70');
> insert into family(familyid,childname,birthdate)
> values(5,'William','04-Apr-73');
>
> insert into family(familyid,childname,birthdate)
> values(9,'Miranda','28-Apr-69');
> insert into family(familyid,childname,birthdate)
> values(9,'Marion','28-Apr-69');
> insert into family(familyid,childname,birthdate)
> values(7,'Murtle','02-Mar-63');
>
> insert into family(familyid,childname,birthdate)
> values(7,'Robert','25-Feb-58');
> insert into family(familyid,childname,birthdate)
> values(7,'Rex','31-Jan-61');
>
> --family 17 has been added in this posting, but was
> --absent from the previous posting in this newsgroup conversation.
> --This familyid should not be retrieved because
> --there are not two children with the names Rex and Robert.
> insert into family(familyid,childname,birthdate)
> values(17,'Robert','30-Jul-58');
> insert into family(familyid,childname,birthdate)
> values(17,'Lilly','12-Jan-61');
>
>
> 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');
>
> FAMILYID
> ----------
> 2
> 2
> 7
> 7
> 17
>
> SQL>
>
>
> Again, my first feeling would be to have a self-join or two and
> propose something like:
>
> SQL>
> SQL> select distinct t1.familyid
> 2 from family t1, family t2
> 3 where t1.familyid=t2.familyid
> 4 and t1.childname='Rex'
> 5 and t2.childname='Robert';
>
> FAMILYID
> ----------
> 2
> 7
>
> SQL>
>
>
> but yet again I ask myself, well for 8 children or 10 children or 12
> children ..., 8 self-joins or 10 self joins or 12 self joins ...,
> scalability etc...
>
> Many thanks
> Colin McGuire
>
> "Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote in message
news:<MeQK9.2271$Sg.933042430_at_newssvr11.news.prodigy.com>...
> > Colin,
> >
> > Working simply with what you gave us (as opposed to what you might be
doing
> > in the end). Why not try:
> >
> > desc family
> > Name Type
> > ----------------------------------- ---------------------------------
> > FAMILYID NUMBER(10)
> > CHILDNAME VARCHAR2(25)
> > BIRTHDATE DATE
> >
> > select * from family order by familyid;
> >
> > FAMILYID CHILDNAME BIRTHDATE
> > ---------- ------------------------- --------------------
> > 1 Robert 01-Jul-0075 00:00:00
> > 2 Mary 21-Aug-0070 00:00:00
> > 2 Felix 21-Sep-0072 00:00:00
> > 2 Robert 14-Jan-0068 00:00:00
> > 2 Rex 17-Feb-0066 00:00:00
> > 5 Douglas 21-Aug-0070 00:00:00
> > 5 William 04-Apr-0073 00:00:00
> > 7 Murtle 02-Mar-0063 00:00:00
> > 7 Robert 25-Feb-0058 00:00:00
> > 7 Rex 31-Jan-0061 00:00:00
> > 9 Miranda 02-Mar-0063 00:00:00
> > 9 Marion 28-Apr-0069 00:00:00
> >
> > 12 rows selected.
> >
> >
> > select familyid
> > from family
> > where familyid in (select familyid
> > from family
> > group by familyid
> > having count(*) >= 2)
> > and childname in ('Robert','Rex');
> >
> > FAMILYID
> > ----------
> > 2
> > 2
> > 7
> > 7
> >
> > When you need to look at a family of 8 with say 'Bert',
'Robert','Douglas'
> > as names, you need only change the having clause and last part of the
where
> > clause.
> >
> > Bertram Moshier
> > Oracle Certified Professional 8i and 9i DBA
> >
> > http://www.bmoshier.net/bertram
> >
> >
> >
> > "Colin McGuire" <colinandkaren_at_lycos.co.uk> 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

How about

select familyid from tbl_family where childname = 'Robert' intersect
select familyid from tbl_family where childname = 'Rex'?

This will give you all the families with at least one of each.

Regards,
Paul Received on Sun Dec 15 2002 - 09:48:52 CST