SQL Query Joins

[sankalp]

Hi Need Some Help on this

I know i can do this using sequential queries but am sure there has to be a better way of doing this.

Have the following Tables

State ---- Fields

StateCode - Primary Key
StateName


Candidate ---- Fields
CandID - Primary Key
ConstCode - Foreign Key
CandName
PartyCode - Foreign Key
CurVotes ( Number of votes that this candidate has got in this constituency)


Constituency -- Fields

ConstCode - Primary Key
StateCode - Foreign Key
ConstName
CandId - Foreign Key

Now What i need is
a report like
For Suppose State A

Const Party A Party B Party C Others Valid Votes
a 15000 2000 3000 20000 40000
b 15000 2000 3000 20000 40000
c 15000 2000 3000 20000 40000
d 15000 2000 3000 20000 40000

where valid votes =( Sum of Votes for A, B,C and D )

and so on for all the constituencies within State A where CurVotes >0

I have to take into considration the top 3 parties and then all the rest go into Others.

Now i have to calculate the Vote share for each party A,B,C and D.
The formula to do this is, suppose for Party A VoteShare= (15000/40000)*100 (= 37.5% )
This Formula give me the share of total votes that party A has got this year


Plus i have a history table for votes information which looks like this

ConstCode
Party
HistVotes

Now have to prepare a report for this as the one above
only keeping in mind those constituencies for which we have prepared the above report.

Now when i get the vote share of say party a ( suppose 22.3% ) i have to calculate Vote change which will be ( Current share of Party A - Previous share of party A
in State A in This Case)
( which in the above case will be 37.5 - 22.3 = 15.2%)

Any Help ????

[berta]

I don't know if I have undestand every thing.
The following is the code for join among the 3 tables:

select
a.Candidate,
a.CandID ,
a.ConstCode,
a.CandName ,
a.PartyCode,
a.CurVotes ,
b.ConstCode,
b.StateCode,
b.ConstName,
b.CandId ,
c.statecode,
c.statename
from
candidate a,
Constituency b,
state c
where
a.constcode=b.constcode and
a.candid=b.candid and
b.statecode=c.statecode

and the following is a possibile select with sum for curvotes group by constcode:

select
a.Candidate,
a.CandID ,
a.ConstCode,
a.CandName ,
a.PartyCode,
sum(a.CurVotes) ,
b.ConstCode,
b.StateCode,
b.ConstName,
b.CandId ,
c.statecode,
c.statename
from
candidate a,
Constituency b,
state c
where
a.constcode=b.constcode and
a.candid=b.candid and
b.statecode=c.statecode
group by statecode

I hope to bring help to U
hi, brt




<center>
<HR width=80%>
<img src='http://web.tiscali.it/bertaplanet/images/bertaplanet.gif'>
</center>