Arithmetic in SQL function using Joins
I am new to SQL. I am trying to perform some arithmetic using an SQL function.
I have three tables:
Table1_ID Table2_ID Value
Table3_ID Table1_ID Table2_ID X_Column
I have the contents of X_Column from Table 3 as input to start with, which will be the input to my function. These are the conditions:
1) Multiple Table2_ID can have the same X_Column input and not vice versa
2) Multiple Table1_ID can have the same Table2_ID and not vice versa
3) Every X_Column input has one or more Table2_IDs; Every Table2_ID has one or more Table1_IDs
All I want to do is multiply and return the contents of the field Value from Table1, which has a Table1_ID, a corresponding Table2_ID, a corrsponding Table3_ID corresponding to an input X_Column, whenever a particular input X_Column is inputted.
I am not able to understand how to do this. I think it is possible using Joins or Views. I don't have any related code to post here yet. Could someone please help?
I'm guessing at the column data types...
Join the tables through the Table2_ID column. When the rows from the different tables are joined, you can multiply the columns: Table1.Value * Table3.X_Column
-- sample tables declare @Table1 table (Table1_ID int, Table2_ID int, Value numeric(18,2)); declare @Table2 table (Table2_ID int, Switch bit); declare @Table3 table (Table3_ID int, Table1_ID int, Table2_ID int, X_Column numeric(18,2)); -- sample data insert @Table1 values(100, 200, 5.00); insert @Table1 values(100, 201, 10.00); insert @Table2 values(200, 0); insert @Table2 values(201, 1); insert @Table3 values(300, 100, 200, 2.0); insert @Table3 values(300, 100, 201, 2.5); declare @X_Column numeric(18,2); set @X_Column = 2.0; select t1.Value * t3.X_Column from @Table1 t1 join @Table2 t2 on t2.Table2_ID = t1.Table2_ID join @Table3 t3 on t3.Table2_ID = t2.Table2_ID where t3.X_Column = @X_Column