Received: from mps2.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145]) by postgresql.org (Postfix) with ESMTP id 3CC2D4759A1 for ; Wed, 27 Mar 2002 10:45:21 -0500 (EST) Received: from vip333 ([218.47.117.28]) by mps2.plala.or.jp with ESMTP id <20020327154523.BHAJ26199.mps2.plala.or.jp@vip333>; Thu, 28 Mar 2002 00:45:23 +0900 Date: Thu, 28 Mar 2002 00:47:12 +0900 From: Masaru Sugawara To: Subject: Re: Problem how to combine to two tables Cc: pgsql-sql@postgresql.org In-Reply-To: <000b01c1d58a$342d6d40$6a866099@iese.fhg.de> References: <000b01c1d58a$342d6d40$6a866099@iese.fhg.de> Errors-To: rk73@sea.plala.or.jp Message-Id: <20020328000758.470E.RK73@sea.plala.or.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.08 X-Archive-Number: 200203/360 X-Sequence-Number: 7234 On Wed, 27 Mar 2002 13:23:29 +0100 "Torsten Willrich" wrote: > This means, that if the two tables are linked properly, that Torsten and > Daniel sit in room no. 201 and Markus in room No. 202. And linking properly > is my problem: > > I want an output like this > > Employee_ID Room ID Employee > {1,3} 201 1 Torsten > {1,3} 201 3 Daniel > {2} 202 2 Markus > > That means, that the SELECT-statement has to be something like this: > SELECT * from Table1,Table2 where Table1.Employee_ID=Table2.ID; Not smart, but probably feasible to link. SELECT t1.e_id AS "Employee_ID", t1.room AS "Room", t1.id AS "ID", t2.employee AS "Employee" FROM table2 AS t2, (SELECT '{'|| employee_id[1] || '}' AS e_id, employee_id[1] AS id, room FROM table1 WHERE employee_id[2] IS NULL UNION SELECT '{'|| employee_id[1] || ',' || employee_id[2] || '}' AS e_id, employee_id[1] AS id, room FROM table1 WHERE employee_id[2] IS NOT NULL UNION SELECT '{'|| employee_id[1] || ',' || employee_id[2] || '}' AS e_id, employee_id[2] AS id, room FROM table1 WHERE employee_id[2] IS NOT NULL ) AS t1 WHERE t2.id = t1.id ORDER BY t1.room, t1.id ; Regards, Masaru Sugawara