master-detail relationship and count
От | Gary Stainburn |
---|---|
Тема | master-detail relationship and count |
Дата | |
Msg-id | 200211291006.06749.gary.stainburn@ringways.co.uk обсуждение исходный текст |
Ответы |
Re: master-detail relationship and count
Re: master-detail relationship and count |
Список | pgsql-sql |
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 nullrtyards | integer | not nullrtname | 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
В списке pgsql-sql по дате отправления: