X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id B22819FA61D for ; Sun, 11 Jun 2006 10:27:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 99906-04 for ; Sun, 11 Jun 2006 10:27:54 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from tomts43-srv.bellnexxia.net (tomts43.bellnexxia.net [209.226.175.110]) by postgresql.org (Postfix) with ESMTP id 0362C9F9CAA for ; Sun, 11 Jun 2006 10:27:53 -0300 (ADT) Received: from acer.sympatico.ca ([65.95.132.134]) by tomts43-srv.bellnexxia.net (InterMail vM.5.01.06.13 201-253-122-130-113-20050324) with ESMTP id <20060611132751.CEXF1543.tomts43-srv.bellnexxia.net@acer.sympatico.ca> for ; Sun, 11 Jun 2006 09:27:51 -0400 Message-Id: <5.2.1.1.0.20060611092112.04a773f0@pop6.sympatico.ca> X-Sender: b1wudu74@pop6.sympatico.ca X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Sun, 11 Jun 2006 09:27:12 -0400 To: pgsql-sql@postgresql.org From: Frank Bax Subject: Re: Finding multiple events of the same kind In-Reply-To: <200606111453.07326.leif@solumslekt.org> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/115 X-Sequence-Number: 25351 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