Ancient postgres+EXCEPT

From: Antti Linno <alligator(at)all(dot)ee>
To: pgsql-sql(at)postgresql(dot)org
Subject: Ancient postgres+EXCEPT
Date: 2000-07-07 07:10:50
Message-ID: Pine.LNX.4.04.10007071002130.18559-100000@all.ee
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Lo. I have in my local machine quite new POstgresql
[PostgreSQL 6.5.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66]
but the machine, I am writing scripts for has some ancient postgres(btw,
how to see the version on psql, if its not shown at the start? ).
I made a query

SELECT ajaperiood.isik_id,struktuuriyksus,ametikood,tase,eesnimi_perenimi
FROM ajaperiood,ametikoht,isik,tootaja
WHERE perioodi_tyyp='tooleping'
AND
(
algus<=timestamp(date_trunc('month', timestamp 'today')-interval '1 day')
OR
algus is NULL
)
AND
(
lopp>=timestamp(date_trunc('month', timestamp 'today')-interval '1 day')
OR
lopp is NULL
)
AND
tootaja.isik_id=ajaperiood.isik_id
AND
ametikoht.id=tootaja.ametikoht_id
AND
ametikoht.ametikood='10'
EXCEPT
SELECT ajaperiood.isik_id,struktuuriyksus,ametikood,tase,eesnimi_perenimi;

and now I have to put it in such state, that doesn't have except, but does
the same thing. I guess something twice as big but with ANDs,ORs, or
smthng like this :(. But I don't have a clue.

As a comments, I can offer you this: I have four tables-
job,workers,persons and timeintervals. Job is connected through workers
with persons(that is the table that binds persons and jobs), and every
person can have multiple time intervals such as 'vacation','contract',
'stopped contract','unpaid vacation' ...
What I need is to get the list of workers, that have contract, but aren't
on vacation etc. It would be nice to get them with one query, but if its
impossible, than I have to code it the hard way *yawn*.

Antti

P.S. Sorry for not translating the field and table names, but I have a
mighty Sandman around me.

Browse pgsql-sql by date

  From Date Subject
Next Message benoit 2000-07-07 08:32:35 Re: MAX() of 0 records.
Previous Message Tom Lane 2000-07-07 07:06:03 Re: [SQL] MAX() of 0 records.