pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding multiple events of the same kind
6+ messages / 3 participants
[nested] [flat]
* Finding multiple events of the same kind
@ 2006-06-11 12:53 Leif B. Kristensen <leif@solumslekt.org>
2006-06-11 13:27 ` Re: Finding multiple events of the same kind Frank Bax <fbax@sympatico.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Leif B. Kristensen @ 2006-06-11 12:53 UTC (permalink / raw)
To: pgsql-sql
I've got two tables:
CREATE TABLE events (
event_id INTEGER PRIMARY KEY,
tag_fk INTEGER NOT NULL REFERENCES tags (tag_id),
place_fk INTEGER NOT NULL REFERENCES places (place_id),
event_date CHAR(18) NOT NULL DEFAULT '000000003000000001',
sort_date DATE NOT NULL DEFAULT '40041024BC',
event_note TEXT NOT NULL DEFAULT ''
);
CREATE TABLE participants (
person_fk INTEGER NOT NULL REFERENCES persons (person_id),
event_fk INTEGER NOT NULL REFERENCES events (event_id) ON DELETE
CASCADE,
is_principal BOOLEAN NOT NULL DEFAULT false,
PRIMARY KEY (person_fk, event_fk)
);
The table "participants" is of course a many-to-many relation
between "events" and "persons". My problem is that it's entirely
possible to insert eg. multiple birth events for one person, and I'd
like to be able to spot these.
I've made this function that will return a birth date, but it will of
course be somewhat undefined in case of multiple events (tag_fk
2=birth, 62=stillbirth, 1035="guesstimated" birth).
CREATE OR REPLACE FUNCTION get_pbdate(INTEGER) RETURNS TEXT AS $$
DECLARE
pb_date TEXT;
BEGIN
SELECT event_date INTO pb_date FROM events, participants
WHERE events.event_id = participants.event_fk
AND participants.person_fk = $1
AND events.tag_fk in (2,62,1035)
AND participants.is_principal IS TRUE;
RETURN COALESCE(pb_date,'000000003000000001');
END;
$$ LANGUAGE plpgsql;
The originating database had a field for "primary" event, along with
some business logic for deciding between multiple events of the same
type, but I don't want to maintain something like that. I'll rather run
a report spotting persons with multiple birth events. Any ideas?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Finding multiple events of the same kind
2006-06-11 12:53 Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
@ 2006-06-11 13:27 ` Frank Bax <fbax@sympatico.ca>
2006-06-11 13:34 ` Re: Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
2006-06-13 10:58 ` Re: Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 2 replies; 6+ messages in thread
From: Frank Bax @ 2006-06-11 13:27 UTC (permalink / raw)
To: pgsql-sql
At 08:53 AM 6/11/06, Leif B. Kristensen wrote:
>I've got two tables:
>
>CREATE TABLE events (
> event_id INTEGER PRIMARY KEY,
> tag_fk INTEGER NOT NULL REFERENCES tags (tag_id),
> place_fk INTEGER NOT NULL REFERENCES places (place_id),
> event_date CHAR(18) NOT NULL DEFAULT '000000003000000001',
> sort_date DATE NOT NULL DEFAULT '40041024BC',
> event_note TEXT NOT NULL DEFAULT ''
>);
>
>CREATE TABLE participants (
> person_fk INTEGER NOT NULL REFERENCES persons (person_id),
> event_fk INTEGER NOT NULL REFERENCES events (event_id) ON DELETE
>CASCADE,
> is_principal BOOLEAN NOT NULL DEFAULT false,
> PRIMARY KEY (person_fk, event_fk)
>);
>
>The table "participants" is of course a many-to-many relation
>between "events" and "persons". My problem is that it's entirely
>possible to insert eg. multiple birth events for one person, and I'd
>like to be able to spot these.
Something like this should get a list of person_fk values that have more
than one birth date:
SELECT participants.person_fk, count(participants.person_fk) FROM events,
participants
WHERE events.event_id = participants.event_fk
AND events.tag_fk in (2,62,1035)
GROUP BY participants.person_fk HAVING count(participants.person_fk) > 1
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Finding multiple events of the same kind
2006-06-11 12:53 Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
2006-06-11 13:27 ` Re: Finding multiple events of the same kind Frank Bax <fbax@sympatico.ca>
@ 2006-06-11 13:34 ` Leif B. Kristensen <leif@solumslekt.org>
2006-06-11 18:36 ` Re: Finding multiple events of the same kind Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 1 reply; 6+ messages in thread
From: Leif B. Kristensen @ 2006-06-11 13:34 UTC (permalink / raw)
To: pgsql-sql
On Sunday 11. June 2006 15:27, Frank Bax wrote:
>SELECT participants.person_fk, count(participants.person_fk) FROM
> events, participants
> WHERE events.event_id = participants.event_fk
> AND events.tag_fk in (2,62,1035)
> GROUP BY participants.person_fk HAVING
> count(participants.person_fk) > 1
That worked like a charm! Thank you very much!
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Finding multiple events of the same kind
2006-06-11 12:53 Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
2006-06-11 13:27 ` Re: Finding multiple events of the same kind Frank Bax <fbax@sympatico.ca>
2006-06-11 13:34 ` Re: Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
@ 2006-06-11 18:36 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-06-11 19:03 ` Re: Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Broersma Jr @ 2006-06-11 18:36 UTC (permalink / raw)
To: Leif B. Kristensen <leif@solumslekt.org>; pgsql-sql
> On Sunday 11. June 2006 15:27, Frank Bax wrote:
> >SELECT participants.person_fk, count(participants.person_fk) FROM
> > events, participants
> > WHERE events.event_id = participants.event_fk
> > AND events.tag_fk in (2,62,1035)
> > GROUP BY participants.person_fk HAVING
> > count(participants.person_fk) > 1
>
> That worked like a charm! Thank you very much!
> --
Also, you could create a unique column constraint that would prevent multiply instances of the
same person in the participants table.
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Finding multiple events of the same kind
2006-06-11 12:53 Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
2006-06-11 13:27 ` Re: Finding multiple events of the same kind Frank Bax <fbax@sympatico.ca>
2006-06-11 13:34 ` Re: Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
2006-06-11 18:36 ` Re: Finding multiple events of the same kind Richard Broersma Jr <rabroersma@yahoo.com>
@ 2006-06-11 19:03 ` Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 0 replies; 6+ messages in thread
From: Leif B. Kristensen @ 2006-06-11 19:03 UTC (permalink / raw)
To: pgsql-sql
On Sunday 11. June 2006 20:36, Richard Broersma Jr wrote:
>Also, you could create a unique column constraint that would prevent
> multiply instances of the same person in the participants table.
I have considered that as well. But as my front end main view looks like
a "structured document" in a browser window, ie. with events and their
associated notes and sources, it's sometimes very convenient to enter
and view duplicate events. It can be of immense help in a critical
evaluation of sources. Here's a screenshot of the main view:
<http://solumslekt.org/temp/exodus1.jpg;
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Finding multiple events of the same kind
2006-06-11 12:53 Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
2006-06-11 13:27 ` Re: Finding multiple events of the same kind Frank Bax <fbax@sympatico.ca>
@ 2006-06-13 10:58 ` Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 0 replies; 6+ messages in thread
From: Leif B. Kristensen @ 2006-06-13 10:58 UTC (permalink / raw)
To: pgsql-sql
My question and your answer have now become part of a blog entry at my
site: <http://solumslekt.org/blog/;. Thank you again.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-06-13 10:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-11 12:53 Finding multiple events of the same kind Leif B. Kristensen <leif@solumslekt.org>
2006-06-11 13:27 ` Frank Bax <fbax@sympatico.ca>
2006-06-11 13:34 ` Leif B. Kristensen <leif@solumslekt.org>
2006-06-11 18:36 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-06-11 19:03 ` Leif B. Kristensen <leif@solumslekt.org>
2006-06-13 10:58 ` Leif B. Kristensen <leif@solumslekt.org>
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