Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 326F24764B2
	for <pgsql-sql@postgresql.org>; Fri, 17 May 2002 12:58:04 -0400 (EDT)
Received: from mps2.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145])
	by postgresql.org (Postfix) with ESMTP id 92B9F47596D
	for <pgsql-sql@postgresql.org>; Fri, 17 May 2002 12:50:33 -0400 (EDT)
Received: from vip1600 ([218.47.116.205]) by mps2.plala.or.jp with ESMTP
	id <20020517165031.VQRS25092.mps2.plala.or.jp@vip1600>;
	Sat, 18 May 2002 01:50:31 +0900
Date: Sat, 18 May 2002 01:51:50 +0900
From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: juerg.rietmann@pup.ch
Subject: Re: how to build this list ?
Cc: pgsql-sql@postgresql.org
In-Reply-To: 
 <OF3F2ABA05.D9201794-ONC1256BBB.00436E1A-C1256BBB.00457696@pup.ch>
References: <OF3F2ABA05.D9201794-ONC1256BBB.00436E1A-C1256BBB.00457696@pup.ch>
Errors-To: rk73@sea.plala.or.jp
Message-Id: <20020518011016.6B66.RK73@sea.plala.or.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
X-Mailer: Becky! ver. 2.00.11
X-Archive-Number: 200205/184
X-Sequence-Number: 7819

On Thu, 16 May 2002 14:38:02 +0200
juerg.rietmann@pup.ch wrote:

> The list I need :
> 
> 11111111 (main order)
>             8888  01 (cylinderid, type)
>             7986  01 (cylinderid, type)
>             8732  01 (cylinderid, type)
>       11111112 (suborder)
>             9876  03 (cylinderid, type)
>             4318  03 (cylinderid, type)
>       11111113 (suborder)
>             7654  02 (cylinderid, type)
>             6653  02 (cylinderid, type)
>       11111114 (suborder)
>       11111115 (suborder)
> 22222221 (main order)
>             8866  03 (cylinderid, type)
>       22222222 (suborder)
>             8796  03 (cylinderid,type)
>             9876  03 (cylinderid,typ)
>       22222223 (suborder)
> 


Hi, Juerg.
Based on the analysis of that relations, I would think  "4532 04 (cylinderid, type)"
should be selected...  Your careless mistake ?   If so, one of the possible
resolution is:


CREATE TABLE tbl_order (orderid text, main boolean);
CREATE TABLE cylinder (cylinderid text, type text, orderid text);

SELECT t.list
  FROM (SELECT NULL AS orderid,
               c1.cylinderid,
               c1.digit7,
               c1.digit1, 
               '            ' || c1.cylinderid || ' ' || c1.type ||
                    ' ' || '(cylinderid, type)' AS list
          FROM tbl_order AS o1 INNER JOIN
               (SELECT cylinderid, type, orderid,
                       substring(orderid from 1 for 7) AS digit7,
                       substring(orderid from 8 for 1) AS digit1
                  FROM cylinder
               ) AS c1 ON (c1.orderid  = o1.orderid)
        UNION
        SELECT o2.orderid,
               NULL AS cylinderid,
               substring(o2.orderid from 1 for 7),
               substring(o2.orderid from 8 for 1),
               CASE WHEN o2.main = true
                    THEN o2.orderid || ' (main order)'
                    ELSE '      ' || o2.orderid || ' (suborder)'
               END AS list
          FROM tbl_order AS o2
      ) AS t
ORDER BY t.digit7, t.digit1, t.orderid, t.cylinderid


Regards,
Masaru Sugawara