Re: sub select madness - Mailing list pgsql-general

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