Re: Help with a subselect inside a view - Mailing list pgsql-general

On Thu, Aug 25, 2005 at 08:19:25 -0700,
  Bill Moseley <moseley@hank.org> wrote:
>
> DROP VIEW cl;
> CREATE VIEW cl  (id, instructor)
>     AS
>         SELECT class.id, person.first_name
>           FROM class, instructors, person
>          WHERE instructors.person = person.id
>            AND class.id = (
>                    SELECT instructors.id
>                      FROM instructors, person
>                     WHERE instructors.class = class.id
>                       AND person.id = instructors.person
>                     LIMIT 1
>                 );
>
> Which returns a row for every row in "instructors" table.

I think if you were to use this approach you would do something more like:

DROP VIEW cl;
CREATE VIEW cl  (id, instructor)
    AS
        SELECT class.id,
            (SELECT person.first_name
               FROM instructors, person
               WHERE instructors.class = class.id
                 AND person.id = instructors.person
               LIMIT 1)
          FROM class;