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