Content-Type: text/plain;
  charset="us-ascii"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: master-detail relationship and count
Date: Fri, 29 Nov 2002 10:06:06 +0000
User-Agent: KMail/1.4.1
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200211291006.06749.gary.stainburn@ringways.co.uk>

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;

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