pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to build this list ?
2+ messages / 2 participants
[nested] [flat]
* how to build this list ?
@ 2002-05-16 12:38 juerg.rietmann@pup.ch
0 siblings, 1 reply; 2+ messages in thread
From: juerg.rietmann@pup.ch @ 2002-05-16 12:38 UTC (permalink / raw)
To: pgsql-sql
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
__________________________________________________
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: how to build this list ?
@ 2002-05-17 16:51 Masaru Sugawara <rk73@sea.plala.or.jp>
parent: juerg.rietmann@pup.ch
0 siblings, 0 replies; 2+ messages in thread
From: Masaru Sugawara @ 2002-05-17 16:51 UTC (permalink / raw)
To: juerg.rietmann@pup.ch; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-05-17 16:51 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-16 12:38 how to build this list ? juerg.rietmann@pup.ch
2002-05-17 16:51 ` 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