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
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 |