Обсуждение: how to build this list ?

Hello there

How to build this list ? I have no idea how to start, thanks for any help
!!

Table order :
orderid           main
------------------------------------------------
11111111    true
11111112    false
11111113    false
22222221    true
11111114    false
11111115    false
22222222    false
22222223    false

Table cylinder :
cylinderid  type  orderid
-------------------------------------------------
8888        01    11111111
8866        03    22222221
7986        01    11111111
8796        03    22222222
9876        03    22222222
8732        01    11111111
9876        03    11111112
7654        02    11111113
4532        04    11111115
4318        03    11111112
6653        02    11111113


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)
 

__________________________________________________

PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland

internet          :  www.pup.ch
phone       : +4141 790 4040
fax         : +4141 790 2545
mobile            : +4179 211 0315
__________________________________________________

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(orderidfrom 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
AScylinderid,              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