pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem how to combine to two tables
3+ messages / 3 participants
[nested] [flat]

* Problem how to combine to two tables
@ 2002-03-27 12:23 Torsten Willrich <willrich@iese.fhg.de>
  2002-03-27 15:23 ` Re: Problem how to combine to two tables Andrew G. Hammond <drew@xyzzy.dhs.org>
  2002-03-27 15:47 ` Re: Problem how to combine to two tables Masaru Sugawara <rk73@sea.plala.or.jp>
  0 siblings, 2 replies; 3+ messages in thread

From: Torsten Willrich @ 2002-03-27 12:23 UTC (permalink / raw)
  To: pgsql-sql

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?






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Problem how to combine to two tables
  2002-03-27 12:23 Problem how to combine to two tables Torsten Willrich <willrich@iese.fhg.de>
@ 2002-03-27 15:23 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Andrew G. Hammond @ 2002-03-27 15:23 UTC (permalink / raw)
  To: Torsten Willrich <willrich@iese.fhg.de>; +Cc: pgsql-sql

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@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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Problem how to combine to two tables
  2002-03-27 12:23 Problem how to combine to two tables Torsten Willrich <willrich@iese.fhg.de>
@ 2002-03-27 15:47 ` Masaru Sugawara <rk73@sea.plala.or.jp>
  1 sibling, 0 replies; 3+ messages in thread

From: Masaru Sugawara @ 2002-03-27 15:47 UTC (permalink / raw)
  To: willrich@iese.fhg.de; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-03-27 15:47 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-27 12:23 Problem how to combine to two tables Torsten Willrich <willrich@iese.fhg.de>
2002-03-27 15:23 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
2002-03-27 15:47 ` Masaru Sugawara <rk73@sea.plala.or.jp>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox