How join 2 table with calculation in the relation ID I'm using SQL Server 2012 for my RDBMS I have 3 main table Tb_Template,Tb_Value, and Tb_Mapping.
I use Tb_Mapping to join Tb_Template and Tb_Value. But the relation to Tb_Value have calculation Val_Now &amp; Val_Before.
What I try is make Value_ID in Tb_Mapping is save query like this (example for ID 3 in Tb_Mapping)
SELECT 
    (SELECT Val_Now FROM Tb_Value WHERE ID = 'VAL4') + 
    (SELECT Val_Now FROM Tb_Value WHERE ID = 'VAL5')

Is there another way to make get result like View_Result
Table Tb_Template
ID    Description 
-----------------
1        Desc1    
2        Desc2        
3        Desc3        
4        Desc4    
5        Desc5 

Table Tb_Value
ID    Val_Now    Val_Before 
----------------------------
VAL1     1            0
VAL2     5            3      
VAL3     1            1
VAL4     3            4
VAL5     0            2

Table Tb_Mapping
ID   Template_ID      Value_ID
-------------------------------
1       1               VAL1
2       2               VAL2      
3       3             VAL4+VAL5
4       4               VAL3
5       5        VAL1+VAL2-VAL4-VAL5

View_Result:
Description   Val_Now Val_Before
----------------------------------
   Desc1          1         0
   Desc2          5         3
   Desc3          3         6
   Desc4          1         1
   Desc5          3        -3
WITH cte AS (SELECT t.Description,
                    '+' + m.Value_ID Value_ID,
                    v.ID Val_ID,
                    v.Val_Now,
                    v.Val_Before
             FROM Tb_Template t
             JOIN Tb_Mapping m ON t.ID = m.Template_ID
             JOIN Tb_Value v ON CHARINDEX(v.ID, m.Value_ID) &gt; 0)
SELECT Description,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID, Value_ID) &gt; 0
                 THEN Val_Now
                 WHEN CHARINDEX('-' + Val_ID, Value_ID) &gt; 0
                 THEN -Val_Now
                 ELSE 0
                 END ) AS Val_Now,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID, Value_ID) &gt; 0
                 THEN Val_Before
                 WHEN CHARINDEX('-' + Val_ID, Value_ID) &gt; 0
                 THEN -Val_Before
                 ELSE 0
                 END ) AS Val_Before
FROM cte
GROUP BY Description;

fiddle (parenthesis in mappings removed).
PS. None Tb_Value.ID value must be a prefix of another Tb_Value.ID value. If false then update CHARINDEX checking expression accordingly.


oh maybe becase the VAL i have is VAL1 - VAL50 , because CHARINDEX() make the result like that ? example VAL9+VAL10 and the result VAL1,VAL9,VAL10 ? – Fikri

WITH cte AS (SELECT t.Description,
                    '+' + m.Value_ID + '+' Value_ID,
                    v.ID Val_ID,
                    v.Val_Now,
                    v.Val_Before
             FROM Tb_Template t
             JOIN Tb_Mapping m ON t.ID = m.Template_ID
             JOIN Tb_Value v ON CHARINDEX(v.ID, m.Value_ID) &gt; 0)
SELECT Description,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID + '+', Value_ID) + CHARINDEX('+' + Val_ID + '-', Value_ID) &gt; 0
                 THEN Val_Now
                 WHEN CHARINDEX('-' + Val_ID + '+', Value_ID) + CHARINDEX('-' + Val_ID + '+', Value_ID) &gt; 0
                 THEN -Val_Now
                 ELSE 0
                 END ) AS Val_Now,
       SUM( CASE WHEN CHARINDEX('+' + Val_ID + '+', Value_ID) + CHARINDEX('+' + Val_ID + '-', Value_ID) &gt; 0
                 THEN Val_Before
                 WHEN CHARINDEX('-' + Val_ID + '+', Value_ID) + CHARINDEX('-' + Val_ID + '+', Value_ID) &gt; 0
                 THEN -Val_Before
                 ELSE 0
                 END ) AS Val_Before
FROM cte
GROUP BY Description;

fiddle