Re: Help with a subselect inside a view

On Thu, Aug 25, 2005 at 08:19:25 -0700,
Bill Moseley <moseley(at)hank(dot)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;