From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: willrich@iese.fhg.de
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem how to combine to two tables
Date: Thu, 28 Mar 2002 00:47:12 +0900
Message-ID: <20020328000758.470E.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <000b01c1d58a$342d6d40$6a866099@iese.fhg.de>
References: <000b01c1d58a$342d6d40$6a866099@iese.fhg.de>
On Wed, 27 Mar 2002 13:23:29 +0100
"Torsten Willrich" <willrich@iese.fhg.de> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rk73@sea.plala.or.jp, willrich@iese.fhg.de
Subject: Re: Problem how to combine to two tables
In-Reply-To: <20020328000758.470E.RK73@sea.plala.or.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox