pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: willrich@iese.fhg.de
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem how to combine to two tables
Date: Thu, 28 Mar 2002 00:47:12 +0900
Message-ID: <20020328000758.470E.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <000b01c1d58a$342d6d40$6a866099@iese.fhg.de>
References: <000b01c1d58a$342d6d40$6a866099@iese.fhg.de>

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





view thread (3+ messages)

Message-ID: <20020328000758.470E.RK73@sea.plala.or.jp>
Permalink:  ../20020328000758.470E.RK73@sea.plala.or.jp/
Also on:    postgresql.org/message-id/20020328000758.470E.RK73@sea.plala.or.jp

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rk73@sea.plala.or.jp, willrich@iese.fhg.de
  Subject: Re: Problem how to combine to two tables
  In-Reply-To: <20020328000758.470E.RK73@sea.plala.or.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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