I am stuck on the best way to iterate through the below SQL tables and perform multiplication of the values in Table 1 against prices in table 2 where string 1 and string 2 match in SQL server, then insert the results into a results table

This data is part of a C# windows app I am developing while learning. Usually, I would just read the tables into a dataset, multiply both tables where needed and add the results to another dataset to use.

This seems a long-winded way of doing this as the table data values and price matrix is 64 * 1600 when potentially I can use a SQL server's resources and return the results more rapidly than using the power on a user's laptop.

Table 1

Table 2

results

I have made this starter code but need to iterate through all the value/price columns in each table, similar to a for loop in C.

SELECT 
ISNULL((table1.[value 1] * table2.[price 1),'0') as [tCost 1]
FROM table1, table2
WHERE table1.[string 1] = table2.[string 1] 
and table1.[string 2] = table2.[string 2]

CodePudding user response：

I'm checking your table, and I think you can use JOIN in your query, follows a example.

Select
    table1.String1, table2.String2, 
    ISNULL(CAST(table1.Value1 * table2.Price1 as float),'0') as [tCost1],
    ISNULL(CAST(table1.Value2 * table2.Price2 as float),'0') as [tCost2]
from 
    table1
INNER JOIN 
    table2 ON table1.String1 = table2.String1 and table1.String2 = table2.String2

the result will be something like that

String1  String2    tCost1  tCost2
red       red        2398    5097
blue      blue       2598    3597
yellow    yellow     2798    5697
green     green      2998    5697
purple    purple     3198    6297

Best Regards