Обсуждение: help with a view (join-query)
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 | 3samuel | 3 | event1 | 1
samuel | 3 | event2 | 2i hope my explanations are understandable :-)
thanks for any hints,
thomas
On Fri, Mar 24, 2006 at 19:36:34 +0100, me@alternize.com wrote: > hi list > > currently in our database, a "person" contains a relation to one or more "events". i now need a view where each personis listed exactly once, and to each person only one (of possible more) events is listed. it does not matter which onesof the events are shown. when doing a normal JOIN, the resultset might return more than one record per person (for eachevent one line). > is there an easy way to build a query that fullfills my needs? i'd rather not check programmatically if i already listedthe person or not. You could use the Postgres specific extension DISTINCT ON to do this. You can even use ORDER BY to make which row you grab deterministic.
thanks alot - DISTINCT ON has exactly the behaviour i was looking for! - thomas ----- Original Message ----- From: "Bruno Wolff III" <bruno@wolff.to> To: <me@alternize.com> Cc: <pgsql-novice@postgresql.org> Sent: Sunday, March 26, 2006 1:59 AM Subject: Re: [NOVICE] help with a view (join-query) > On Fri, Mar 24, 2006 at 19:36:34 +0100, > me@alternize.com wrote: >> 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. > > You could use the Postgres specific extension DISTINCT ON to do this. You > can > even use ORDER BY to make which row you grab deterministic. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >