Обсуждение: Problem how to combine to two tables

Ok, to explain my problem, it is the easiest way, to make an example:

let's say we have two tables:

Table1 looks like this:

Employee_ID        Room
------------------------
{1,3}            201
{3}            202

And Table2 looks like this:

ID    Employee
---------------
1    Torsten
2    Markus
3    Daniel

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;

But of course this doesn't work, because I can't compare an int to a set of
int :-(

Does anyone know how to do this correct?

On Wed, Mar 27, 2002 at 01:23:29PM +0100, Torsten Willrich wrote:
> Ok, to explain my problem, it is the easiest way, to make an example:
>
> let's say we have two tables:
>
> Table1 looks like this:
>
> Employee_ID        Room
> ------------------------
> {1,3}            201
> {3}            202
>
> And Table2 looks like this:
>
> ID    Employee
> ---------------
> 1    Torsten
> 2    Markus
> 3    Daniel
>
> 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;
>
> But of course this doesn't work, because I can't compare an int to a set of
> int :-(
>
> Does anyone know how to do this correct?

Don't use arrays.  It's a relational database, use a table and join:

CREATE TABLE rooms (room_id SERIAL PRIMARY KEY,room_name TEXT NOT NULL );

CREATE TABLE employees (employee_id SERIAL PRIMARY KEY,employee_name TEXT NOT NULL );

CREATE TABLE locations (location_id SERIAL PRIMARY KEY,employee_id INTEGER REFERENCES employees,room_id INTEGER
REFERENCESrooms ); 

INSERT INTO rooms (room_name) VALUES ('201');
INSERT INTO rooms (room_name) VALUES ('202');

INSERT INTO employees (employee_name) VALUES ('Al');
INSERT INTO employees (employee_name) VALUES ('Bob');
INSERT INTO employees (employee_name) VALUES ('Chuck');

INSERT INTO locations (employee_id, room_id) VALUES (1,1);
INSERT INTO locations (employee_id, room_id) VALUES (2,1);
INSERT INTO locations (employee_id, room_id) VALUES (3,2);

SELECT r.room_name, e.employee_name
FROM rooms r, employees e, locations l
WHERE r.room_id = l.room_id AND e.employee_id = l.employee_id
ORDER BY r.room_name DESC;

--
Andrew G. Hammond  mailto:drew@xyzzy.dhs.org  http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F              613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

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