help with a view (join-query)

From: <me(at)alternize(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: help with a view (join-query)
Date: 2006-03-24 18:36:34
Message-ID: 03f601c64f71$e172be50$0201a8c0@iwing
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

hi list

currently in our database, a "person" contains a relation to one or more "events". i now need a view where each person is listed exactly once, and to each person only one (of possible more) events is listed. it does not matter which ones of the events are shown. when doing a normal JOIN, the resultset might return more than one record per person (for each event one line).
is there an easy way to build a query that fullfills my needs? i'd rather not check programmatically if i already listed the person or not.

here's some sample data:

table person
(person_name | person_id):
------------------
thomas | 1
barbara | 2
samuel | 3

table events
(event_name | event_id):
------------------
event1 | 1
event2 | 2
event3 | 3

table personevents
(person_id | event_id) :
----------------------------
1 | 1
1 | 2
1 | 3
2 | 3
3 | 1
3 | 2

one possible result i need would look like the following
(person_name | person_id | event_name | event_id)
---------------------------------
thomas | 1 | event1 | 1
barbara | 2 | event3 | 3
samuel | 3 | event1 | 1

while all the join-variants i know of return something like
(person_name | person_id | event_name | event_id):
----------------------------------
thomas | 1 | event1 | 1
thomas | 1 | event2 | 2
thomas | 1 | event3 | 3
barbara | 2 | event3 | 3
samuel | 3 | event1 | 1
samuel | 3 | event2 | 2

i hope my explanations are understandable :-)

thanks for any hints,
thomas

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Pierre Thibaudeau 2006-03-24 19:06:14 Re: Indexes and inheritance
Previous Message Alan Hodgson 2006-03-24 17:32:37 Re: Indexes and inheritance