Help with a subselect inside a view
От | Bill Moseley |
---|---|
Тема | Help with a subselect inside a view |
Дата | |
Msg-id | 20050825061217.GA5388@hank.org обсуждение исходный текст |
Ответы |
Re: Help with a subselect inside a view
(David Fetter <david@fetter.org>)
Re: Help with a subselect inside a view (Bruno Wolff III <bruno@wolff.to>) |
Список | 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@hank.org
В списке pgsql-general по дате отправления: