Received: from xyzzy.lan.internal (cvx-west-206-47-80-67.kingston.net [206.47.80.67]) by postgresql.org (Postfix) with ESMTP id 2E50D475DD3 for ; Wed, 27 Mar 2002 10:23:14 -0500 (EST) Received: from drew by xyzzy.lan.internal with local (Exim 3.35 #1 (Debian)) id 16qFGc-00025J-00; Wed, 27 Mar 2002 10:23:14 -0500 Date: Wed, 27 Mar 2002 10:23:14 -0500 To: Torsten Willrich Cc: pgsql-sql@postgresql.org Subject: Re: Problem how to combine to two tables Message-ID: <20020327152314.GD7777@xyzzy.dhs.org> References: <000b01c1d58a$342d6d40$6a866099@iese.fhg.de> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="8nsIa27JVQLqB7/C" Content-Disposition: inline In-Reply-To: <000b01c1d58a$342d6d40$6a866099@iese.fhg.de> User-Agent: Mutt/1.3.27i From: "Andrew G. Hammond" X-Fnord: +++ath X-WebTV-Stationery: Standard; BGColor=black; TextColor=black X-Message-Flag: Message text blocked: ANTI-MICROSOFT OPINIONS X-BeenThere: drew@xyzzy.dhs.org X-Archive-Number: 200203/359 X-Sequence-Number: 7233 --8nsIa27JVQLqB7/C Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable 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: >=20 > let's say we have two tables: >=20 > Table1 looks like this: >=20 > Employee_ID Room > ------------------------ > {1,3} 201 > {3} 202 >=20 > And Table2 looks like this: >=20 > ID Employee > --------------- > 1 Torsten > 2 Markus > 3 Daniel >=20 > 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 proper= ly > is my problem: >=20 > I want an output like this >=20 > Employee_ID Room ID Employee > {1,3} 201 1 Torsten > {1,3} 201 3 Daniel > {2} 202 2 Markus >=20 > That means, that the SELECT-statement has to be something like this: > SELECT * from Table1,Table2 where Table1.Employee_ID=3DTable2.ID; >=20 > But of course this doesn't work, because I can't compare an int to a set = of > int :-( >=20 > 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=20 FROM rooms r, employees e, locations l WHERE r.room_id =3D l.room_id AND e.employee_id =3D l.employee_id ORDER BY r.room_name DESC; --=20 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= =20 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me --8nsIa27JVQLqB7/C Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjyh4+IACgkQCT73CrRXhLHrdwCfSLnnygypBUdedJHOHYBpL6un wNsAnAzYkmGWwzqc//5Q+TeErARYljYi =+5cc -----END PGP SIGNATURE----- --8nsIa27JVQLqB7/C--