Re: sub select madness

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