| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Syntax Query - sorry
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');
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
Received on Sun Dec 15 2002 - 07:24:19 CST
 |
 |