From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Finding multiple events of the same kind
Date: Sun, 11 Jun 2006 09:27:12 -0400
Message-ID: <5.2.1.1.0.20060611092112.04a773f0@pop6.sympatico.ca> (raw)
In-Reply-To: <200606111453.07326.leif@solumslekt.org>
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
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: fbax@sympatico.ca
Subject: Re: Finding multiple events of the same kind
In-Reply-To: <5.2.1.1.0.20060611092112.04a773f0@pop6.sympatico.ca>
* 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