Thread: select-querries out of an array?
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.
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