Ok, all the suggestions were good.
I think I'll stick with Marcin Mank's query for now, I'll also try to work further with Richard Broersma's query later.
Thank you guys, you were really helpful.
On 7/14/06, Marcin Mank <marcin.mank@gmail.com> wrote:
----- Original Message -----
From: "Sergio Duran" <sergioduran@gmail.com>
To: <pgsql-general@postgresql.org >
Sent: Thursday, July 13, 2006 9:20 PM
Subject: [GENERAL] I need help creating a query
> Hello,
>
> I need a little help creating a query, I have two tables, worker and
> position, for simplicity sake worker only has its ID and its name,
position
> has the ID of the worker, the name of his position, a date, and his
salary/
>
> worker: worker_id, name
> position: position_id, worker_id, position, startdate, salary
>
> If I perfom a query joining both tables, I can obtain all the workers and
> the positions the've had.
>
> SELECT name, startdate, position, salary FROM worker JOIN position
> USING(worker_id);
> worker1 | 2001-01-01 | boss | 999999
> worker2 | 2001-01-01 | cleaning | 100
> worker2 | 2006-04-01 | programmer | 20000
> worker2 | 2006-07-04 | management | 25000
>
> so far so good, now I need to obtain all the workers only with the
position
> they had on a given date.
> if I wanted to know the positions on '2006-05-01' it would return
> worker1 | 2001-01-01 | boss | 999999
> worker2 | 2006-04-01 | programmer | 20000
>
This should work:
select distinct on(W.worker_id) W.name,P.position,P.salary
from worker W,position P
where P.worker_id=W.worker_id
and 'SOME DATE' >= P.startdate
order by W.worker_id,P.startdate
Cheers
Marcin