Re: Problem how to combine to two tables

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