[postgis-users] OT: Help with a join

Marc Weber marco-oweber at gmx.de
Mon Jun 30 08:31:09 PDT 2008


On Mon, Jun 30, 2008 at 11:16:54AM -0500, Stephen Woodbridge wrote:
>  Hi all,
> 
>  I could use a little help with a join:
> 
>  select a.tlid, b.fullname, b.paflag, a.mtfcc, c.fromhn, c.tohn, c.side, 
>  c.zip, c.plus4, a.statefp, a.countyfp
>    from featnames b, addr c, addrfn d, edges a
>   where a.tlid=b.tlid and b.tlid=c.tlid and b.linearid=d.linearid and 
>  c.arid=d.arid and a.roadflg='Y'
>   order by a.tlid, b.fullname, c.side ;
> 
>  I need to get back records for every record in edges even if there is no 
>  matching tlid in the addr table.
> 
>  How do I do that?

The SELECT foo FROM a,b,c,d WHERE a.id = b.id AND b.id=c.id
is called inner join.
Just have a look at the Postgresql manual Joins are described in depth
at the beginning you want a LEFT or RIGHT JOIN here, such as

SELECT a.id, b.id FROM tablea as a LEFT JOIN tableb as b ON (a.id =b.id)
This includes all from a and fills missing rows from b with nulls

Marc



More information about the postgis-users mailing list