From: "Dan Langille" <dan@langille.org>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Date: Fri, 29 Nov 2002 09:24:37 -0500
MIME-Version: 1.0
Subject: Re: master-detail relationship and count
Cc: pgsql-sql@postgresql.org
Message-ID: <3DE73255.29778.E71B8DFA@localhost>
References: <200211291051.07175.gary.stainburn@ringways.co.uk>
In-reply-to: <Pine.LNX.4.44.0211291315420.4251-100000@matrix.gatewaynet.com>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

On 29 Nov 2002 at 13:16, Achilleus Mantzios wrote:

> On Fri, 29 Nov 2002, Gary Stainburn wrote:
> 
> > As you can see from the extract below, your statement has worked for all
> > landmarks that have links, but ignores any landmarks with out links.  How can
> > I adjust this so that all landmarks are listed, but with a zero count where
> > appropriate?
> 
> Then, use LEFT OUTER JOIN ... USING (),
> in combination with COALESCE().
> 
> (read the docs)

When it comes to outer joins, this page is quite useful:

http://www.postgresql.org/idocs/index.php?explicit-joins.html

I was reading it yesterday when improving the speed of some JOINs.  
The JOIN went from 3440ms to about 18ms when following the advice on 
that page.

I've put the queries and the explain output at 
http://www.freshports.org/tmp/outer-join.txt.  This will be an 
example of an OUTER JOIN but not the master-detail situation which 
occurs elsewhere in the database.

cheers

-- 
Dan Langille : http://www.langille.org/