Обсуждение: sub select madness
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 Cheers Tony Grant -- www.tgds.net Library management software toolkit, redhat linux on Sony Vaio C1XD, Dreamweaver MX with Tomcat and PostgreSQL
table structure for the query (i used in testing):
====================================
create table expo (expo_number int not null, exponame varchar(100) not null,
primary key(expo_number));
create table artist (artist_number int not null, artist_name varchar(100)
not null, primary key(artist_number));
create table expo_artist (expo_number int not null, artist_number int not
null, primary key (expo_number, artist_number), foreign key (expo_number)
references expo(expo_number), foreign key (artist_number) references
artist(artist_number));
Data inserted (used in testing)
=======================
insert into expo values (1, 'cool expo');
insert into expo values (2, 'crap expo');
insert into expo values (3, 'mediocre expo');
insert into artist values (1, 'John');
insert into artist values (2, 'Jill');
insert into artist values (3, 'Jack');
insert into expo_artist values (1, 1);
insert into expo_artist values (1, 2);
insert into expo_artist values (2, 2);
insert into expo_artist values (3, 2);
insert into expo_artist values (3, 3);
Query that I ran:
================
SELECT t1.expo_number,
t1.exponame
from
expo t1,
expo_artist t2
where t2.artist_number in (
select artist_number
from artist
where artist_name = 'John')
and t1.expo_number = t2.expo_number;
returned correctly with the above query...
Regards,
Carl
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tony Grant
Sent: 06 March 2003 10:29 PM
To: postgres list
Subject: [GENERAL] sub select madness
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
Cheers
Tony Grant
--
www.tgds.net Library management software toolkit,
redhat linux on Sony Vaio C1XD,
Dreamweaver MX with Tomcat and PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I need a sub select that gets the list of expos that each work of art > has been shown at. > ... > for each work using just SQL? Or do I have to call a function to get > this to work? Should be possible in SQL. Send in a description of your tables. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200303061011 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+Z2UpvJuQZxSWSsgRArE6AKCiB1F85/+Pfy4TrVVnCxnecRGkwACgsqqz O4hnseQllaxxdcR1ocTKTbA= =OJCY -----END PGP SIGNATURE-----
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I think what you want is this (I used 'Jill' not 'John' as in your example because John has no expos in the sample data) SELECT a.artist_name, e.expo_number, e.exponame FROM artist a, expo e, expo_artist ea WHERE a.artist_name= 'Jill' AND a.artist_number = ea.artist_number AND e.expo_number = ea.expo_number - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200303061053 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+Z28bvJuQZxSWSsgRAm4PAKCeUHlD7VgkIo4edAEgsERQPwApeACgywR4 VAc52mvNjrxApy4BtElZqow= =bJXQ -----END PGP SIGNATURE-----