Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.8.8/8.8.8) with ESMTP id LAA25235 for ; Tue, 6 Oct 1998 11:15:19 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.5/8.8.5) with ESMTP id RAA19944; Tue, 6 Oct 1998 17:08:35 +0200 (IST) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <199810061603.3142469.6@onyx.floppy.org> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Tue, 6 Oct 1998 17:09:41 +0200 To: "Gemeinschaft Studienarbeit Datenbanken" From: Herouth Maoz Subject: Re: [SQL] select-querries out of an array? Cc: pgsql-sql@postgreSQL.org At 17:03 +0200 on 6/10/98, Gemeinschaft Studienarbeit Datenbanken wrote: > When I now would like to have all games (Spiel*) selected, which come > on CD as media (Datentraegertyp is array which contains CD, Disks, misc, > etc.), I would type the sql request like this: > > select name from Spiel* where Datentraegertyp = '{"CD"}'; > > which only lists all games which *ONLY* come on CD (pretty clear as the > match is '=' on the single array entry. How do I formulate the request when > I would like to have those which have Datentraegertyp like '{"CD", >"Diskette"}' > or '{"Tape", "Diskette", "CD"}' ? > > I hope you get what I am up to ;-) > > Thanks once again for hints. I never liked arrays in databases. They are not the right implementation, at least not from the classical relational point-of-view, for what you have in mind. I tend to look at arrays as representing a bulk of information (An icon, an IP). And that's exactly what you can do with it in PostgreSQL: Retrieve the entire array and use it within the application, write an entire array, etc. The relational approach to your configuration is to have a second table, relating to your main table through the primary key. So, it has two fields - "Bestellnummer" (whatever that is) and "Datentraegertyp". For one game in the Spiel* tables, you can have several rows in this secondary table - one row for each media on which this particular game appears. Then you join. SELECT .... FROM Spiel* s, MediaTable m WHERE ..... AND s.Bestellnummer = m.Bestellnummer AND m.Datentraegertyp in ( 'CD', 'Diskette', 'Tape' ); I haven't tried this with an object-oriented table (with that * after the table name). Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma