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

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;

which comes back with the error:

[gary@larry gary]$ psql <route.sql |more
ERROR:  Relation 'subsel' does not exist
[gary@larry gary]$

Gary

On Friday 29 Nov 2002 11:16 am, Achilleus Mantzios wrote:
> 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

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