Re: Finding multiple events of the same kind

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