Re: SQL Syntax Query

From: Bert Bear <bertbear_at_NOSPAMbertbear.net>
Date: Sun, 15 Dec 2002 00:43:24 GMT
Message-ID: <MeQK9.2271$Sg.933042430@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

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 Sat Dec 14 2002 - 18:43:24 CST

Re: SQL Syntax Query - sorry