Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.3/8.11.1) with SMTP id f2Q1Zmw94333 for ; Sun, 25 Mar 2001 20:35:48 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from davisson.uni2.net (davisson.uni2.net [129.142.7.104]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2NDDGw49648 for ; Fri, 23 Mar 2001 08:13:17 -0500 (EST) (envelope-from DNJP@Gyldendal.dk) Received: from gyldendal.dk (gyldendal.dk [130.227.242.133]) by davisson.uni2.net (8.10.0/8.9.1) with SMTP id f2NDDDk02943 for ; Fri, 23 Mar 2001 14:13:13 +0100 Received: from GBgate-Message_Server by gyldendal.dk with Novell_GroupWise; Fri, 23 Mar 2001 14:10:37 +0100 Message-Id: X-Mailer: Novell GroupWise 5.5.2 Date: Fri, 23 Mar 2001 14:10:26 +0100 From: "Jens Philipsen" To: Subject: Hi there, having problems with the following: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id f2NDDIw49649 X-Archive-Number: 200103/501 X-Sequence-Number: 2172 Hi there, having problems with the following: On Postgresql (on Linux) I have 3 tables: 1)employee firstname lastname initials 2)has_title initials title 3)has_trade initials trade Now, what I would like is to get a list of all employees with all their titles and trades - employees can have zero or several titles/trades. For testing purposes I made a version in MS Access, in which this works: (SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel, null AS fag FROM medarbejder, har_titel, har_fag where medarbejder.initialer=har_titel.initialer) UNION (SELECT medarbejder.initialer, fornavn, efternavn, telefon, null AS titel, fag FROM medarbejder, har_fag, har_titel WHERE medarbejder.initialer=har_fag.initialer) But in Postgresql no such luck: SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel as titel, null AS fag FROM medarbejder, har_titel, har_fag WHERE medarbejder.initialer=har_titel.initialer UNION SELECT medarbejder.initialer, fornavn, efternavn, telefon, null as titel, fag AS fag FROM medarbejder, har_fag, har_titel WHERE medarbejder.initialer=har_fag.initialer Only produces an error: "unable to transform varchar to unknown Each UNION | EXCEPT | INTERSECT clause must have compatible target" Any suggestions? Thanx in advance Jens