Re: Problem how to combine to two tables

On Wed, 27 Mar 2002 13:23:29 +0100
"Torsten Willrich" <willrich(at)iese(dot)fhg(dot)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