[postgis-users] OT: Help with a join
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
More information about the postgis-users