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