Re: SQL confusion

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
theoldest person       SELECT MIN(birthdate)       FROM name in2        WHERE in2.famnu = outername.famnu)
 
);


Richard