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 по дате отправления:

Предыдущее
От: Greg Stark
Дата:
Сообщение: Re: Postgresql replication
Следующее
От: Chris Travers
Дата:
Сообщение: Re: Postgresql replication