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 REFERENCES rooms );

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(at)xyzzy(dot)dhs(dot)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