Thread: master-detail relationship and count
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
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
On Friday 29 Nov 2002 10:06 am, 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; You'll want to GROUP BY SELECT r.rtid, r.rtname, l.count(*) FROM route r, links l WHERE l.lktype='R' AND l.lklid=r.rtid GROUP BY r.rtid, r.rtname; -- Richard Huxton
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?
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.sqlrtid | 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
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
On 29 Nov 2002 at 13:16, 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) When it comes to outer joins, this page is quite useful: http://www.postgresql.org/idocs/index.php?explicit-joins.html I was reading it yesterday when improving the speed of some JOINs. The JOIN went from 3440ms to about 18ms when following the advice on that page. I've put the queries and the explain output at http://www.freshports.org/tmp/outer-join.txt. This will be an example of an OUTER JOIN but not the master-detail situation which occurs elsewhere in the database. cheers -- Dan Langille : http://www.langille.org/
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
On Fri, Nov 29, 2002 at 02:39:50PM +0000, Gary Stainburn wrote: > 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; Hmm, I think this should work:select r.rtid, r.rtname, subsel.cnt from route r left outer join (select r2.rtid as rid, count(lnk.lklid)as cnt from route r2, links lnk where lnk.lktype='R' and lnk.lklid = r2.rtid group by r2.rtid) as subselonr.rtid = subsel.rid; At least, it won't error. I don't have any test data to see if it returns what you want. Ross