Help with a subselect inside a view

From: Bill Moseley <moseley(at)hank(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Help with a subselect inside a view
Date: 2005-08-25 06:12:17
Message-ID: 20050825061217.GA5388@hank.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I need a little SQL help:

I'm trying to get a subselect working inside a view.

I have a table "class" that has related tables (a class has a
location, a location has an address with columns city, state, zip).
I want to use a VIEW to display columns related to a given class.

But a class can also have one or more instructors. So I have a link
table:

Table "public.instructors"
Column | Type | Modifiers
--------+---------+-----------
person | integer | not null
class | integer | not null

Foreign-key constraints:
"$1" FOREIGN KEY (person) REFERENCES person(id)
"$2" FOREIGN KEY ("class") REFERENCES "class"(id)

I can do the following, but in the (very rare) case where there may be
two instructors assigned to the class I will get two rows back.

CREATE VIEW class_list
(
id, name, class_time, location, location_name,
address, city, state, zip,
instructor_name
)
AS
SELECT class.id, class.name, class.class_time, class.location,
location.name,
address.id, address.city, address.state, address.zip,
person.last_name

FROM class, location, address,
instructors, person

WHERE class.location = location.id
AND location.address = address.id
AND location.region = region.id
-- Not what I want
AND instructors.person = person.id
AND instructors.class = class.id;

I'm completely happy to just fetch just one of the instructors, and
don't care which one. I just need only one row per class. (I assume
that's my hint right there.)

I can select a single instructor from a given class like:

SELECT person.id
FROM instructors, person
WHERE instructors.class = 555
AND person.id = instructors.person
LIMIT 1;

So I thought I might be able to add that as a subselect to the VIEW,
but I have not been able to make it work. I suspect I'm missing
something obvious.

Thanks,

Oh BTW -- If I do a count(*) and a WHERE that only includes columns in
the "class" table on the VIEW, will Postgresql still do the joins? Or
will it only do the select on the "class" table. I suspect it will
do the joins to make sure the relations can be found.

--
Bill Moseley
moseley(at)hank(dot)org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Travers 2005-08-25 06:29:44 Re: Postgresql replication
Previous Message Aly Dharshi 2005-08-25 00:46:34 Re: Postgresql replication