SQL Syntax Query - sorry

[Bert Bear]

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

[url]http://www.bmoshier.net/bertram[/url]



"Colin McGuire" <colinandkaren@lycos.co.uk> wrote in message
news:ab6cea37.0212141239.2b94bc48@posting.google.c om...

> 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

[Vladimir M. Zakharychev]

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) [url]http://www.dpsp-yes.com[/url]
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@lycos.co.uk> wrote in message
news:ab6cea37.0212150524.c5e8b5d@posting.google.co m...

> 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@NOSPAMbertbear.net> wrote in message

news:<MeQK9.2271$Sg.933042430@newssvr11.news.prodi gy.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
> >
> > [url]http://www.bmoshier.net/bertram[/url]
> >
> >
> >
> > "Colin McGuire" <colinandkaren@lycos.co.uk> wrote in message
> > news:ab6cea37.0212141239.2b94bc48@posting.google.c om...

> > > 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

[Tony Cantara]

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


[email]colinandkaren@lycos.co.uk[/email] (Colin McGuire) wrote in message news:<ab6cea37.0212141239.2b94bc48@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

[Paul Brewer]

"Colin McGuire" <colinandkaren@lycos.co.uk> wrote in message
news:ab6cea37.0212150524.c5e8b5d@posting.google.co m...

> 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@NOSPAMbertbear.net> wrote in message

news:<MeQK9.2271$Sg.933042430@newssvr11.news.prodi gy.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
> >
> > [url]http://www.bmoshier.net/bertram[/url]
> >
> >
> >
> > "Colin McGuire" <colinandkaren@lycos.co.uk> wrote in message
> > news:ab6cea37.0212141239.2b94bc48@posting.google.c om...

> > > 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

[Peter J. Holzer]

On 2002-12-15 13:24, Colin McGuire <colinandkaren@lycos.co.uk> wrote:

> 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).
>

[test data omitted]

>
> 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');

The queries are reversed: You have to filter for the children's names
first and then count the results:

SQL> edit
Wrote file afiedt.buf

1 select familyid from (
2 select distinct familyid, childname from family
3 where childname in ('Robert','Rex')
4 )
5 group by familyid
6* having count(*) = 2
SQL> /

FAMILYID
----------
2
7

(The distinct is to ignore multiple children with the same name in the
family - if that isn't possible, remove it and get rid of one sort)

hp

--
_ | Peter J. Holzer | Schlagfertigkeit ist das, was einem
|_|_) | Sysadmin WSR | auf dem Nachhauseweg einfällt.
| | | [email]hjp@hjp.at[/email] | -- Lars 'Cebewee' Noschinski in dasr.
__/ | [url]http://www.hjp.at/[/url] |

[Bert Bear]

Tony,

One problem is if a familyID has two Roberts (and no Rex).

I wondering if FamilyID and Childname are unique? (I know you don't know.)

Colin? Are FamilyID and Childname unique are non-unique?

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

[url]http://www.bmoshier.net/bertram[/url]


"Tony Cantara" <tonycantara123@hotmail.com> wrote in message
news:3d0c2563.0212150701.2a037488@posting.google.c om...

> 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
>
>
> [email]colinandkaren@lycos.co.uk[/email] (Colin McGuire) wrote in message

news:<ab6cea37.0212141239.2b94bc48@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

[Bert Bear]

Peter,

Sorry!! Doing to many things at one time and I put sneakers / tennis shoes
in mouth.

Your query of:

select familyid from (select distinct familyid, childname from family where
childname in ('Robert','Rex')) group by familyid having count(*) = 2;

Does indeed solve the problems, thus far discussed, EVEN having say two
"Roberts" with different birthdates in family 17.

( e.g. insert into family values(17,'Robert','17-JAN-47'); )

I realized you had the correct answer while watching a TV show (MSNBC show
about prisons - no less) and thought hey, use distinct (or group by) to
eliminate the duplicate Roberts. Ah, isn't hat what Pete was doing, I also
thought.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

[url]http://www.bmoshier.net/bertram[/url]




"Bert Bear" <bertbear@NOSPAMbertbear.net> wrote in message
news:sObL9.2825$411.1227700344@newssvr11.news.prod igy.com...

> Peter,
>
> One problem is if a family has two Roberts and no Rex. I wonder if
> "FamilyID" and "Childname" can be a composite Primary Key?
>
> Colin?!
>
> Will "FamilyID" and "Childname" be unique (e.g. able to be a composite
> Primary Key?)?
>
> Bertram Moshier
> Oracle Certified Professional 8i and 9i DBA
>
> [url]http://www.bmoshier.net/bertram[/url]
>
>
> "Peter J. Holzer" <hjp-usenet@hjp.at> wrote in message
> news:slrnavpi7k.kq1.hjp-usenet@teal.hjp.at...

> > On 2002-12-15 13:24, Colin McGuire <colinandkaren@lycos.co.uk> wrote:

> > > 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).
> > >

> > [test data omitted]

> > >
> > > 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');

> >
> > The queries are reversed: You have to filter for the children's names
> > first and then count the results:
> >
> > SQL> edit
> > Wrote file afiedt.buf
> >
> > 1 select familyid from (
> > 2 select distinct familyid, childname from family
> > 3 where childname in ('Robert','Rex')
> > 4 )
> > 5 group by familyid
> > 6* having count(*) = 2
> > SQL> /
> >
> > FAMILYID
> > ----------
> > 2
> > 7
> >
> > (The distinct is to ignore multiple children with the same name in the
> > family - if that isn't possible, remove it and get rid of one sort)
> >
> > hp
> >
> > --
> > _ | Peter J. Holzer | Schlagfertigkeit ist das, was einem
> > |_|_) | Sysadmin WSR | auf dem Nachhauseweg einfällt.
> > | | | [email]hjp@hjp.at[/email] | -- Lars 'Cebewee' Noschinski in dasr.
> > __/ | [url]http://www.hjp.at/[/url] |

>
>

[Bert Bear]

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

[url]http://www.bmoshier.net/bertram[/url]


"Tony Cantara" <tonycantara123@hotmail.com> wrote in message
news:3d0c2563.0212150701.2a037488@posting.google.c om...

> 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
>
>
> [email]colinandkaren@lycos.co.uk[/email] (Colin McGuire) wrote in message

news:<ab6cea37.0212141239.2b94bc48@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

[Colin McGuire]

"Bert Bear" <bertbear@NOSPAMbertbear.net> wrote in message news:<sObL9.2825

> Will "FamilyID" and "Childname" be unique (e.g. able to be a composite
> Primary Key?)?
>

Yes.
Colin

[Bert Bear]

Colin,

I think Peter with his query:

select familyid from (select distinct familyid, childname from family where
childname in ('Robert','Rex')) group by familyid having count(*) = 2;

has the definitive answer. Let us know how it turns out for you.

Bert.

"Colin McGuire" <colinandkaren@lycos.co.uk> wrote in message
news:ab6cea37.0212160635.7b985d40@posting.google.c om...

> "Bert Bear" <bertbear@NOSPAMbertbear.net> wrote in message

news:<sObL9.2825

>

> > Will "FamilyID" and "Childname" be unique (e.g. able to be a composite
> > Primary Key?)?
> >

>
> Yes.
> Colin

[Mike Sherrill]

On Mon, 16 Dec 2002 03:31:36 GMT, "Bert Bear"
<bertbear@NOSPAMbertbear.net> wrote:

>One problem is if a family has two Roberts and no Rex. I wonder if
>"FamilyID" and "Childname" can be a composite Primary Key?

In this application, maybe. In the most general case, no.

A "family"--a word that deserves more careful definition than it
usually gets--should accommodate adoptions and the children of
divorced parents (which can become a special case of adoptions).

A "family" might also simply include children of other parents. I've
seen this myself in migrant workers, where parents might leave their
children with a relative or close friend while they follow the
harvest. (I'd argue that one's a family and the other's a household,
and I'd hope there were other useful terms, too. <g>)

--
Mike Sherrill
Information Management Systems