Content-Type: text/plain;
  charset="iso-8859-1"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Subject: Re: master-detail relationship and count
Date: Fri, 29 Nov 2002 10:51:07 +0000
User-Agent: KMail/1.4.1
Cc: pgsql-sql@postgresql.org
References: <Pine.LNX.4.44.0211291229170.4251-100000@matrix.gatewaynet.com>
In-Reply-To: <Pine.LNX.4.44.0211291229170.4251-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200211291051.07175.gary.stainburn@ringways.co.uk>

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@larry gary]$ psql -d nymr <route.sql
 rtid |   rtname   | cnt
------+------------+-----
    1 | The Grange |   1
(1 row)
[gary@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@matrix.gatewaynet.com
>         mantzios@softlab.ece.ntua.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