Re: SQL Syntax Query

From: Peter J. Holzer <hjp-usenet_at_hjp.at>
Date: Sun, 15 Dec 2002 19:24:52 +0100
Message-ID: <slrnavpi7k.kq1.hjp-usenet@teal.hjp.at>

On 2002-12-15 13:24, Colin McGuire <colinandkaren_at_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)

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

Received on Sun Dec 15 2002 - 12:24:52 CST

Re: SQL Syntax Query - sorry