pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect-querries out of an array?
2+ messages / 2 participants
[nested] [flat]
* select-querries out of an array?
@ 1998-10-06 15:03 Gemeinschaft Studienarbeit Datenbanken <oodbms@floppy.org>
0 siblings, 1 reply; 2+ messages in thread
From: Gemeinschaft Studienarbeit Datenbanken @ 1998-10-06 15:03 UTC (permalink / raw)
To: PostgreSQL SQL Mailing List <pgsql-sql@hub.org>; +Cc: Datenbanksysteme Studienarbeit <oodbms@floppy.org>
Hiho,
I have a question which neither the samples (as far as I was able to
figure out) or the documentation were able to clear up.
I have a table definition like
(btw. why isn't the primary key of the Spiel-class inherited by the
children classes???)
create table Spiel (
Name text not null,
Bestellnummer text not null primary key,
HerstellerID int4 not null,
Erscheinungsdatum datetime,
Preisempfehlung money not null,
Beschreibung text[],
Computertyp text[] not null,
Datentraegertyp text[] not null,
ClueBook text[] );
create table rollenspiel (
Bestellnummer text not null primary key,
System text not null,
AnzahlCharaktere int2 not null,
Typ text not null,
SpielstandEditor text[] ) inherits ( spiel );
create table adventure (
Bestellnummer text not null primary key,
Schwierigkeitsgrad int2 not null ) inherits ( spiel );
create table simulation (
Bestellnummer text not null primary key,
Typ text[] not null,
AnzahlChars int2 not null ) inherits ( spiel );
create table action (
Bestellnummer text not null primary key,
Eingabegeraet text[] not null,
AnzahlSpieler int2 not null ) inherits ( spiel );
with several subclasses which inherit attributes from the parent class.
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] select-querries out of an array?
@ 1998-10-06 15:09 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Gemeinschaft Studienarbeit Datenbanken <oodbms@floppy.org>
0 siblings, 0 replies; 2+ messages in thread
From: Herouth Maoz @ 1998-10-06 15:09 UTC (permalink / raw)
To: Gemeinschaft Studienarbeit Datenbanken <oodbms@floppy.org>; +Cc: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1998-10-06 15:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-06 15:03 select-querries out of an array? Gemeinschaft Studienarbeit Datenbanken <oodbms@floppy.org>
1998-10-06 15:09 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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