Here's a description of the scenario. The question I'm asking follows
the description.
3 tables
table1:
job_no int4
rate1 float4
qty1 float4
rate2 float4
qty2 float4
rate3 float4
qty3 float4
table2:
job_no int4
part_id int4
rate float4
qty float4
table3:
job_no int4
desc varchar(32)
rate float4
qty float4
These are the condensed table structures. Table 1 will only have 1
record per job. This is the 1 of the 1-to-many relationship to table2
and table3. Table2 has 2 records in it, and table3 has 3 records in
it. What I want to retrieve is the sum of all of the rate*qty.
Therefore, in a single field in the SQL statement, I want
((rate1*qty1)+(rate2*qty2)+(rate3*qty3))+(sum of table2
(rate*qty))+(sum of table3 (rate*qty)). Then I have to apply some
discounts and taxes to this, but that is the next phase....
For example:
table1:
job_no 500
rate1 1500
qty1 1
rate2 500
qty2 1
rate3 75
qty3 10
table2: (2 records)
job_no 500
part_id 3, 10
rate 500, 2000
qty 2, 1
table3: (3 records)
job_no 500
desc 'text1','text2','text3'
rate 100, 150, 200
qty 5, 5, 5
The cost for the example should work out to be:
table1 (2750) + table2 (3000) + table3 (2250) = 8000
Thoughts?
Say we forget about the rates and qtys in table 1 for now. If we just
try and calculate the total for the rates and qtys in table2 and table3
for job_no 500...? break it down even further and return 2 columns, 1
column for the total of table1 and 1 column for the total of table2...?
In my query, where I do:
select
table1.job_no,sum(table2.rate*table2.qty),sum(table3.rate*table3.qty)
from table1,table2,table3 where table1.job_no=500 and
table1.job_no=table2.job_no and table1.job_no=table3.job_no group by
job_no
the result I get is
500, 9000, 2250
the sum of table2 is being multiplied by the number of records in
table3. I understand why, but don't know how to resolve it....
If I do:
select
((rate1*qty1)+(rate2+qty2)+(rate3*qty3)),(table2.rate*table2.qty),(table3.rate*table3.qty)
from table1,table2,table3 where table1.job_no=500 and
table1.job_no=table2.job_no and table1.job_no=table3.job_no
the result is
2750, 1000, 500
2750, 1000, 750
2750, 1000, 1000
2750, 2000, 500
2750, 2000, 750
2750, 2000, 1000
Therefore, if I include a SUM() on these fields, you see where the
problem lies....the result would be:
16500, 9000, 4500
what I want is 2750, 3000, 2250
Thoughts?