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