Re: Help with a subselect inside a view

On Wed, Aug 24, 2005 at 11:12:17PM -0700, Bill Moseley wrote:
> 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.)

This sounds like a case for PostgreSQL's nifty DISTINCT ON functionality.

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

HTH :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!