CREATE TABLE "EIMAKSA"
(
"KORTER" INTEGER NOT NULL,
"KULU" INTEGER NOT NULL,
CONSTRAINT "PK_EIMAKSA" PRIMARY KEY ("KORTER", "KULU")
);

CREATE TABLE "KORTERID"
(
"ID" INTEGER NOT NULL,
"MAJA" VARCHAR( 50) CHARACTER SET WIN1252 NOT NULL,
"KORTER" VARCHAR( 5) CHARACTER SET WIN1252 NOT NULL,
"NIMI" VARCHAR( 50) CHARACTER SET WIN1252 NOT NULL,
"ULDPIND" NUMERIC( 5, 1) NOT NULL,
CONSTRAINT "PK_KORTERID" PRIMARY KEY ("ID", "MAJA", "KORTER")
);


I want to SUM(ULDPIND) for all KORTER's which are not in table EIMAKSA where
MAJA='Kurni 34' and KULU=5.
I managed to create select

SELECT DISTINCT KORTER,ULDPIND FROM KORTERID,EIMAKSA WHERE ID=KORTER AND
MAJA='Kurni 34' AND ID NOT IN(SELECT KORTER FROM EIMAKSA WHERE KULU=5).
(or something like that, I wrote it now from memory)
... but I couldn't create SUM(ULDPIND).
Is it possible? Are there any kind better exclusive joins where I can select
everything from one table that do not exist in another table



Riho-Rene Ellermaa
senior programmer
Hansabank