Re: Problem how to combine to two tables

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