From: | Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
---|---|
To: | "Gemeinschaft Studienarbeit Datenbanken" <oodbms(at)floppy(dot)org> |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] select-querries out of an array? |
Date: | 1998-10-06 15:09:41 |
Message-ID: | l03110706b23fdff8eec3@[147.233.159.109] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mauro Bartolomeoli | 1998-10-07 08:51:59 | random tuple |
Previous Message | Gemeinschaft Studienarbeit Datenbanken | 1998-10-06 15:03:30 | select-querries out of an array? |