Re: master-detail relationship and count

As you can see from the extract below, your statement has worked for all
landmarks that have links, but ignores any landmarks with out links. How can
I adjust this so that all landmarks are listed, but with a zero count where
appropriate?

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
where r.rtid = subsel.rid;
[gary(at)larry gary]$ psql -d nymr <route.sql
rtid | rtname | cnt
------+------------+-----
1 | The Grange | 1
(1 row)
[gary(at)larry gary]$

Gary

On Friday 29 Nov 2002 10:36 am, Achilleus Mantzios wrote:
> On Fri, 29 Nov 2002, Gary Stainburn wrote:
> > Hi folks.
> >
> > I've got a master detail relationship where I have a railway route table
> > listing landmarks along the route, and a Links table listing URL's
> > associated with that landmark. Listed below:
> >
> > How can I do a query showing the landmark ID, the landmark name, and a
> > count of links associated with that landmark. Below is a SQL statement
> > that although is illegal, gives a good idea of what I'm looking for.
> >
> > select r.rtid, r.rtname, l.count(*) from route r, links l where l.lktype
> > = 'R' and l.lklid = r.rtid;
>
> 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.type='R'
> and lnk.lklid = r2.rtid group by r2.rtid) as subsel
> where r.rtid = subsel.rid
>
> or something like that.
>
> > nymr=# \d route
> > Table "route"
> > Attribute | Type | Modifier
> > ------------+-----------------------+------------------------------------
> >-------------- rtid | integer | not null default
> > nextval('route_rtid_seq'::text)
> > rtmile | integer | not null
> > rtyards | integer | not null
> > rtname | character varying(40) |
> > rtspeed | integer |
> > rtgradient | integer |
> > rtsection | integer |
> > rtphone | character(1) |
> > rtcomments | text |
> > Indices: route_index,
> > route_rtid_key
> >
> > nymr=# select r.rtid, l.count(*) from route r, links l where
> > nymr=# \d links
> > Table "links"
> > Attribute | Type | Modifier
> > -----------+-----------------------+-------------------------------------
> >------------ lkid | integer | not null default
> > nextval('staff_sid_seq'::text)
> > lkdesc | character varying(40) |
> > lkurl | character varying(40) |
> > lktype | character(1) |
> > lklid | integer |
> > Index: links_lkid_key
> >
> > lktype indicates the link type - 'R' indicates a route entry
> > lklid indicates the link ID. For a 'R' it is the rtid of the route entry
> > --
> > Gary Stainburn
> >
> > This email does not contain private or confidential material as it
> > may be snooped on by interested government parties for unknown
> > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> ==================================================================
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> Nikis 4, Glyfada
> Athens 16610
> Greece
> tel: +30-10-8981112
> fax: +30-10-8981877
> email: achill(at)matrix(dot)gatewaynet(dot)com
> mantzios(at)softlab(dot)ece(dot)ntua(dot)gr

--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000