Re: master-detail relationship and count

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/