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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
