From: | Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: master-detail relationship and count |
Date: | 2002-11-29 10:36:05 |
Message-ID: | Pine.LNX.4.44.0211291229170.4251-100000@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2002-11-29 10:51:07 | Re: master-detail relationship and count |
Previous Message | Richard Huxton | 2002-11-29 10:35:12 | Re: master-detail relationship and count |