Re: Help with a subselect inside a view

On Thu, Aug 25, 2005 at 08:05:36AM -0500, Bruno Wolff III wrote:
> On Wed, Aug 24, 2005 at 23:12:17 -0700,
>   Bill Moseley <moseley@hank.org> wrote:
> > I need a little SQL help:
> >
> > I'm trying to get a subselect working inside a view.
>
> Unfortunately you didn't show us what you tried. My guess would be that
> you didn't enclose the subselect in parenthesis.

No, it wasn't that.  I just didn't want to look too foolish. ;)

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.


> The distinct on solution that was suggested is probably a better way to
> go anyway.

Turns out it is, Thanks.


--
Bill Moseley
moseley@hank.org