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
