Re: sub select madness - Mailing list pgsql-general
From | Antti Haapala |
---|---|
Subject | Re: sub select madness |
Date | |
Msg-id | Pine.GSO.4.44.0303061710140.2383-100000@paju.oulu.fi Whole thread Raw |
In response to | sub select madness (Tony Grant <tony@tgds.net>) |
List | pgsql-general |
On 6 Mar 2003, Tony Grant wrote: > This one is giving me a headache... > > I have a list of works of art that is returned when I search on the > artists name. > > I need a sub select that gets the list of expos that each work of art > has been shown at. > > Can I have a sub select that shows > > year, expo title, organiser, galerie > year, expo title, organiser, galerie > year, expo title, organiser, galerie > repeats as necessary > > for each work using just SQL? Or do I have to call a function to get > this to work? > > Hope I have been clear Do you mean this: create table expo ( expo_id serial primary key, year int, title text, organiser text, gallery text ); create table artwork ( art_id serial primary key, name text, artist text ); create table artwork_in_expos ( expo_id int8 not null references expo(expo_id) on delete cascade on update cascade, art_id int8 not null references artwork(art_id) on delete cascade on update cascade, unique (expo_id, art_id) ); create view artworks_and_expos as select * from artwork left join artwork_in_expos using (art_id) left join expo using (expo_id); Given data as below Table artwork: art_id | name | artist --------+---------------------+------------- 1 | The void | Artist N.N. 2 | The meaning of life | Artist N.N. 3 | The Bar | Mr Foo Table expo_id: expo_id | year | title | organiser | gallery ---------+------+----------+-----------+--------- 1 | 1838 | The Expo | N.N. | Louvre 2 | 1841 | The Expo | N.N. | Louvre Table artowork_in_expos: expo_id | art_id ---------+-------- 1 | 1 2 | 1 2 | 2 select * from artworks_and_expos gives expo_id | art_id | name | artist | year | title | organiser | gallery ---------+--------+---------------------+-------------+------+----------+-----------+--------- 1 | 1 | The void | Artist N.N. | 1838 | The Expo | N.N. | Louvre 2 | 1 | The void | Artist N.N. | 1841 | The Expo | N.N. | Louvre 2 | 2 | The meaning of life | Artist N.N. | 1841 | The Expo | N.N. | Louvre | 3 | The Bar | Mr Foo | | | | Notice that because of left joins work number 3 is listed once although it hasn't been in any gallery. Now you can do any kind of queries on this view. For example: select * from artworks_and_expos where artist = 'Mr Foo'; expo_id | art_id | name | artist | year | title | organiser | gallery ---------+--------+---------+--------+------+-------+-----------+--------- | 3 | The Bar | Mr Foo | | | | select gallery, year, name from artworks_and_expos where name in ('The void', 'The Bar') and gallery is not null; gallery | year | name ---------+------+---------- Louvre | 1838 | The void Louvre | 1841 | The void (2 rows) select * from artworks_and_expos where expo_id = 2; expo_id | art_id | name | artist | year | title | organiser | gallery ---------+--------+---------------------+-------------+------+----------+-----------+--------- 2 | 1 | The void | Artist N.N. | 1841 | The Expo | N.N. | Louvre 2 | 2 | The meaning of life | Artist N.N. | 1841 | The Expo | N.N. | Louvre (2 rows) Views really rock. Just hour ago I read Mysql docs and it said that they haven't got views because no-one is using them in web apps. It felt they were saying that "SQL gurus always write explicit joins in their queries..." or "For the sake of speed we don't adopt such purely aesthethic features from inferior DBs. And we still have more advanced ALTER TABLE than PgSQL." -- Antti Haapala
pgsql-general by date: