DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=GL0nmGNRqekyXd7nOwInc1cX0a6zueB90J+eCQyCqPfN+TD0M85OUNPcvyyb92hu0EZHgLaKpUiBmOu8GPBeuebvBRETFjbK+q/EbP0wj4SAO75qv2AN9x7XEjuQTfcxVUE8njkuEpJeM4pPaG840F+nqOQeucL2vzHOp/3DXUI=
Message-ID: <a97c7703050922133934cb6728@mail.gmail.com>
Date: Fri, 23 Sep 2005 02:09:00 +0530
From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Reply-To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: "Leif B. Kristensen" <leif@solumslekt.org>
Subject: Re: Help with a view
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200509222012.15463.leif@solumslekt.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <F18A6F7CF1661F46920F2CF713122FED46CCAD@mail.aveo.aveopharma.com>
	<200509222012.15463.leif@solumslekt.org>

> Basically, what I've got is the first person and the tag_type. I can do
> it with a function from PHP:
>
> function get_spouses($p) {
>     $handle =3D pg_query("select person from principals
>                        where event in (select event from principals
>                        where person =3D $p and tag_type =3D " .MARR. ")
>                        order by sort_date");

What about adding an extra condition ' and person <> $p ?

                       select person from principals as a
                        where event in (select event from principals as b
                        where person =3D $p and tag_type =3D " .MARR. ")
                        and  a.person <> $p
                        order by sort_date

>pgslekt=3D> select * from principals where event=3D15821;
>person | event | place | sort_date  | tag_type
>--------+-------+-------+------------+----------
>   2 | 15821 |  1152 | 1999-09-17 |        4
>   3 | 15821 |  1152 | 1999-09-17 |        4
(2 rows)
>I'd also like a view that gives me the two rows combined into one, with
>"person" replaced by p1 and p2.

might consider grouping by all other columns except person and use an
aggregate function over person ,


CREATE VIEW blah AS select list(person) as persons , event , place ,
sort_date , tag_type from principals group by event , place ,
sort_date , tag_type ;

(list is a custom function in my database , currently not posted)

not sure though if i got your problem exactly.

regds
mallah.


>     $i=3D0;
>     $spouses =3D array();
>     while ($row =3D pg_fetch_row($handle) {
>         if $row[0] !=3D $p
>              $spouses[$i++] =3D $row[0];
>     }
>     return $spouses;
> }