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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
