Date: Thu, 5 Dec 2002 13:38:47 -0600
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: master-detail relationship and count
Message-ID: <20021205193847.GA20976@wallace.ece.rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	Gary Stainburn <gary.stainburn@ringways.co.uk>,
	pgsql-sql@postgresql.org
References: <Pine.LNX.4.44.0211291315420.4251-100000@matrix.gatewaynet.com>
	<200211291439.50595.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200211291439.50595.gary.stainburn@ringways.co.uk>
User-Agent: Mutt/1.4i

On Fri, Nov 29, 2002 at 02:39:50PM +0000, Gary Stainburn wrote:
> I've worked out a way of doing it by vreating a view for the tally info as:
> 
> create view link_tally as
>   select lklid, lktype, count(*) from links group by lklid, lktype;
> 
> and then doing:
> 
> select r.rtid, r.rtname, l.count from route r
> left outer join link_tally l on r.rtid = l.lklid and l.lktype = 'R';
> 
> (this works apart from the coalesce bit which I haven't worked out where to 
> put yet, and for the moment isn't important as NULL is okay as a result).
> 
> However, I still can't get it to work straight from the tables.  The nearest 
> I'ev got is:
> 
> select r.rtid, r.rtname, subsel.cnt from route r,
> (select r2.rtid as rid, count(lnk.lkid) as cnt from route r2, links lnk 
> where lnk.lktype='R' and lnk.lklid = r2.rtid  group by r2.rtid) as subsel
> left outer join subsel on r.rtid = subsel.rid;

Hmm, I think this should work:
 
 select r.rtid, r.rtname, subsel.cnt from route r left outer join 
 (select r2.rtid as rid, count(lnk.lklid) as cnt from route r2, links lnk 
 where lnk.lktype='R' and lnk.lklid = r2.rtid  group by r2.rtid) as subsel
 on r.rtid = subsel.rid;

At least, it won't error. I don't have any test data to see if it returns 
what you want.

Ross