| 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
Colin,
after thinking and trying for a while, I was unable to come up with pure SQL solution for this problem, but I made a function, which can be used here. Function's like this:
create or replace function qualifyFamily
( children children_nt,
required_names children_nt, -- can be simple table of varchar2
min_children number default 0
) return number
is
l_cnt number;
begin
if children.count < min_children then
return -1;
end;
select count(*) into l_cnt from
(select childname from table(cast(required_names as children_nt))
minus
select childname from table(cast(children as children_nt))
);
return l_cnt;
end qualifyFamily;
The idea here is that function will return 0 if, and only if children contain all required_names and min_children is satisfied. You can use this function against your original table with nested table column as follows:
select familyid from tbl_family
where qualifyFamily(children,
children_nt(
children_ty('Rex',sysdate),
children_ty('Robert',sysdate)
),
2) = 0
Second collection parameter may be of different nested table type (we don't need DOB component for our logic).
But I was unable to construct similar query without the function (couldn't figure out how to do that select count(*) from (c minus r) in a subquery properly - seems that I can't reference parent table from second level subquery, so I can't push children column down and substract it from required names set.) Probably in 9i, with WITH clause, this is possible, but in 8i it doesn't seem to be. Anyway, function works with memory structures and shouldn't penalize query performance severely. And it allows you to change query conditions easily to satisfy various requirements, like number of siblings and required names.
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "Colin McGuire" <colinandkaren_at_lycos.co.uk> wrote in message news:ab6cea37.0212150524.c5e8b5d_at_posting.google.com...Received on Sun Dec 15 2002 - 08:11:31 CST
> 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
 |
 |