Date: Sat, 9 Oct 2004 22:11:45 +0100
From: Richard Poole <rp@guests.deus.net>
To: pgsql-sql@postgresql.org
Subject: Re: SQL confusion
Message-ID: <20041009211145.GA8572@guests.deus.net>
References: <20041009203945.33114.qmail@web51609.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20041009203945.33114.qmail@web51609.mail.yahoo.com>
User-Agent: Mutt/1.5.4i

On Sat, Oct 09, 2004 at 01:39:45PM -0700, Andrew Ward wrote:
> 
> I'm running postgres 7.3.2 on linux, and making my
> requests from Perl scripts using DBD::Pg.  My table
> structure is as follows (irrelevant cols removed)
> 
> CREATE TABLE name (
>     namecounter integer NOT NULL,
>     firstmiddle character varying(64) NOT NULL,
>     lastname character varying(64) NOT NULL,
>     birthdate date,
>     hh smallint,
>     famnu integer,
> );

This may not be the fastest query, but it should be quite comprehensible.
Lightly tested only on 7.4.5 (do you know why you're not using 7.4.x?).

SELECT namecounter       -- and whatever other columns you need
FROM name outername      -- alias so it's clear what the joins do
WHERE hh = 1             -- simple case: head of family
OR (
    hh = 0           -- not a head
    AND NOT EXISTS ( -- there is no head of this family
        SELECT namecounter
        FROM name in1 
        WHERE hh = 1 AND in1.famnu = outername.famnu)
    AND birthdate = ( -- this person is as old as the oldest person
        SELECT MIN(birthdate)
        FROM name in2 
        WHERE in2.famnu = outername.famnu)
);


Richard