SQL QUERIES ... EXPERT HINTS NEEDED !

From: miguel <jmiguel_at_cet.pt>
Date: 1997/07/08
Message-ID: <01bc8ba4$2fdad820$0a0a2c0a@jmiguel.cet.pt>#1/1

SQL QUERIES ... EXPERT HINTS NEEDED ! The problem is the following:

Consider the 3 tables TOWNS, DEMOG, POST:

TOWNS (primary key: DD,CC,TT)

DD (District code)
CC (County code)
TT (Town code)

TNAME (Town name)

DEMOG (foreign key: TT)

TT (Town code)
POP (Town population)

POST (primary key: PO_ID)

PO_ID (Post Office uinque ID)

DD (District code)
CC (County code)
TT (Town code)


---------------------

Consider also the existing relations:

TOWNS (TT) --------- ONE-TO-ONE-----------> (TT) DEMOG TOWNS (DD,CC,TT) --------- ONE-TO-MANY-----------> (DD,CC,TT) POST The hierachy for DD,CC,TT is the following: One District --> Many Counties, One County --> Many Towns

Query needed: Given one District (DD=x) , obtain Post Office count, and total Population, for all counties (grouped by Counties).
Can this be done in a single query ?

I have tried several queries and I usually get the right count of Post Offices but I get huge Sums of population. It seems that it keeps adding up the population of a same town, for every ocurrence of a Post Office. How can I solve this redundancy problem ?

Thanks in advance.

Miguel Received on Tue Jul 08 1997 - 00:00:00 CDT