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?
