Listing multiple joins on one row
Ivan K.
Given two simple tables tracking the marriages between
people:
>> create table person (
>> person_id serial not null, primary key (person_id),
>> name text not null
>> );
>>
>> create table marriage (
>>
>> marriage_id serial not null, primary key (marriage_id),
>> husband_id int not null, foreign key (husband_id) references person (person_id),
>> wife_id int not null, foreign key (wife_id) references person (person_id),
>> marriage_date timestamp null
>> );
The simple query:
>> select distinct
>> person.person_id,
>> person.name,
>> marriage.marriage_id,
>> marriage.marriage_date
>> from
>> person
>> left join marriage on
>> (person.person_id = marriage.husband_id or person.person_id = marriage.wife_id)
>> order by
>> person.name,
>> marriage.marriage_date;
will produce multiple rows per person.name, if that particular
person has been married multiple times, such as:
>> person_id name marriage_id marriage_date
>> 34 fred 124 2002-01-23
>> 212 sally 63 1995-06-01
>> 212 sally 1221 2004-08-01
>> 169 george 143 1988-02-22
WHAT I WOULD LIKE TO DO, is to collapse the rows
of multiple people into one row and have a query result
that looks like this:
>> person_id name marriage_id_1 marriage_date_1 marriage_id_2 marriage_date_2
>> 34 fred 124 2002-01-23
>> 212 sally 63 1995-06-01 1221 2004-08-01
>> 169 george 143 1988-02-22
no matter how many marriages any one person
may have had.
Can such a query be written entirely with SQL?
Or is there a common stored procedure that people
use?
Thanks!
Mladen Gogala
> WHAT I WOULD LIKE TO DO, is to collapse the rows of multiple people into
> one row and have a query result that looks like this:
>
>>> person_id name marriage_id_1 marriage_date_1 marriage_id_2
>>> marriage_date_2 34 fred 124 2002-01-23
>>> 212 sally 63 1995-06-01 1221 2004-08-01 169 george
>>> 143 1988-02-22
>
> no matter how many marriages any one person may have had.
>
>
> Can such a query be written entirely with SQL? Or is there a common
> stored procedure that people use?
>
> Thanks!
I had a similar question the other day and Laurenz suggested using the
unnest function.
Ivan K.
the "create aggregate" statement can help me.
First, I create this:
CREATE AGGREGATE textcat_all(
basetype = text,
sfunc = textcat,
stype = text,
initcond = ''
);
And then, this query will do what I want:
select
person.person_id,
person.name,
textcat_all( marriage.marriage_id || '; ') as marriage_ids,
textcat_all( marriage.marriagedate || '; ') as marriage_dates
from
person
left join marriageon
(person.person_id = marriage.husband_id or person.person_id =
marriage.wife_id)
group by
person.person_id,
person.birthdate
order by
person.name;
The only problem remaining, is that
I would like the marriage dates to be in order.
The following query does that, but it is
_very_ slow. Does anybody have a suggestion?
select
person.person_id,
person.name,
textcat_all( blah_blah.marriage_id || '; ') as marridage_ids,
textcat_all( blah_blah.marriagedate || '; ') as marriage_dates
from
person
left join
(
select
marriage.marriage_id,
marriage.marriage_date,
marriage.husband_id,
marriage.wife_id
from marriage
order by marriage.marriage_date
)
blah_blah on
(person.person_id = blah_blah.husband_id or person.person_id =
blah_blah.wife_id)
group by
person.person_id,
person.birthdate
order by
person.name;
Thanks!
Bob Badour
> person
> left join
> (
> select
> marriage.marriage_id,
> marriage.marriage_date,
> marriage.husband_id,
> marriage.wife_id
> from marriage
> order by marriage.marriage_date
> )
> blah_blah on
> (person.person_id = blah_blah.husband_id or person.person_id =
> blah_blah.wife_id)
This is just a guess so your mileage may vary. The order by marriage
date does not help the join on person id. I would try prefixing the
order with the person id, but the twist is you want to join on 2
different person ids. I suggest trying something along the lines of:
person
left join (
select
husband_id as person_id
, marriage_date
, marriage_id
from marriage
union
select
wife_id as person_id
, marriage_date
, marriage_id
from marriage
order by person_id, marriage_date
) holy_union
on person.person_id = holy_union.person_id
--
is there something in it for them, like maybe bailouts, if they can
panic us into doing something politically to cover them?
November 19, 2007 - John S Bolton
Ivan K.
> , marriage_date
> , marriage_id
> from marriage
> union
> select
> wife_id as person_id
> , marriage_date
> , marriage_id
> from marriage
> order by person_id, marriage_date
> ) holy_union
> on person.person_id = holy_union.person_id
That is an outstanding solution.
It sorts the marriages by date and is
_fast_. Your "holy_union" humor is
much appreciated too.
I need to learn more about the union
operator.
Thanks!
agryppa
I am in need to start a project that would be postgres based and which
would allow for the management of catholic parishes.
That means it would have to tackle the different "books" (read
baptismal records, other sacraments), and persons. Most of it is
already well set and standardized within catholic parishes around the
world.
My question is at which point to start with such a project? Is the main
difficulty in configuring the database relations or is it elswhere?
Thanks for any answer?
Agryppa
Jasen Betts
start with a list of goals, then an E-R diagram, then define your
relations and tables, then look at defining some extra indices
needed to speed up the critical queries
--- news://freenews.netfront.net/ - complaints: ne...@netfront.net ---