Search Postgresql Archives

Re: left outer join on multi tables

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

 



Thank you, But there seems to be syntax errors. Could you help?


Richard Broersma Jr wrote:
> > 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend



[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