Re: master-detail relationship and count

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 subselon 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