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(at)hank(dot)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(at)hank(dot)org