From: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
To: Leif B. Kristensen <leif@solumslekt.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with a view
Date: Fri, 23 Sep 2005 02:09:00 +0530
Message-ID: <a97c7703050922133934cb6728@mail.gmail.com> (raw)
In-Reply-To: <200509222012.15463.leif@solumslekt.org>
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 = pg_query("select person from principals
> where event in (select event from principals
> where person = $p and tag_type = " .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 = $p and tag_type = " .MARR. ")
and a.person <> $p
order by sort_date
>pgslekt=> select * from principals where event=15821;
>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=0;
> $spouses = array();
> while ($row = pg_fetch_row($handle) {
> if $row[0] != $p
> $spouses[$i++] = $row[0];
> }
> return $spouses;
> }
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah.rajesh@gmail.com, leif@solumslekt.org
Subject: Re: Help with a view
In-Reply-To: <a97c7703050922133934cb6728@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox