Date: Mon, 12 Aug 2002 15:06:40 +0200
From: Andre Schubert <andre@km3.de>
To: pgsql-sql@postgresql.org
Subject: Need Help for select
Message-Id: <20020812150640.4acd538b.andre@km3.de>
Organization: km3 teledienst GmbH
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi all,

i need help to build a select query or plpgsql-fucntion
for the following tables.

create table a (
id int,
name varchar(20)
)

create table b (
a_id int,
c_id int
)

create table c (
b_id int,
d_id int
)

create table d (
id int,
name varchar(20)
)
Is it possible to build a select query that selects d.name for each a.name where
a.id = b.a_id and d.id = c.d_id and each b.c_id must exist in c.b_id.

Example:
a:	      b:             c  :           d:
 id | name      a_id | c_id    b_id | d_id    id |  name
----|-------  -------|-----  -------|-----  -----|--------
 1  | A_Name1    1   |   1       1  |  1      1  | D_Name1
 2  | A_Name2    1   |   2       2  |  1      2  | D_Name2
 3  | A_Name3    2   |   1       3  |  2      3  | D_Name3
 4  | A_Name4    3   |   3       4  |  2
                 3   |   4       5  |  3
	         4   |   5

i wish to have to following result:
--------|--------
A_Name1 | D_Name1
A_Name3 | D_Name2
A_Name4 | D_Name3

I hope someone could understand the problem

Thanks in advance and sorry for my bad english