Re: left outer join on multi tables - Mailing list pgsql-general

> table name
> {
>    id integer primary key
>    first_name text,
>    middle_name text,
>    last_name text
> }
>
> table address
> {
>    id integer primary key
>    number int,
>    street text,
>    city text,
>    state text
> }
>
> table work
> {
>    id integer primary key
>    hours text,
>    shift
> }
>
> table person
> {
>     id integer primary key
>     namid integer references name(id),
>     addressid integer referenes address(id),
>     workid integer references work(id)
> }
> nameid, addressid or workid in person table may be empty.
> I would like to make a view which shows all information about a person
> in one row
> first_name, last_name, street, city, workhours, ...
> In the peron table, if  the workid, addressid, or nameid is empty, just
> do not display the correspodent information.
> I think I need left outer join, but I do not know how to use it. Could
> anyone help?

CREATE VIEW AS
SELECT N.first_name,
       N.last_name,
       A.street,
       A.city,
       W.hour,
       ....
FROM   person P
LEFT JOIN ON (P.namid = N.id)
LEFT JOIN ON (P.addressid = A.id)
LEFT JOIN ON (P.workid = W.id)
;

Hopefully this is all correct and is what you are looking for?

Regards,

Richard Broersma Jr.