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
Date: Thu, 5 Dec 2002 13:38:47 -0600
Message-ID: <20021205193847.GA20976@wallace.ece.rice.edu> (raw)
In-Reply-To: <200211291439.50595.gary.stainburn@ringways.co.uk>
References: <Pine.LNX.4.44.0211291315420.4251-100000@matrix.gatewaynet.com>
<200211291439.50595.gary.stainburn@ringways.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu, gary.stainburn@ringways.co.uk
Subject: Re: master-detail relationship and count
In-Reply-To: <20021205193847.GA20976@wallace.ece.rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox