Re: master-detail relationship and count - Mailing list pgsql-sql

On Fri, 29 Nov 2002, Gary Stainburn wrote:

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

Then, use LEFT OUTER JOIN ... USING (),
in combination with COALESCE().

(read the docs)

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

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