Search Postgresql Archives

Re: Help with a subselect inside a view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux