Ignor Repeat Calling Function inside Query Rows just for Once to every User ID

hiii every body ….

i have three tables for examples

First (Master_Table)

Master_ID | Name
----------------
     1    | John
     2    | Max
     3    | Liza

Second (Activites_Table)

Activ_ID  | Activity_Name | Master_ID
-------------------------------
     1    |  Swimming     |     1
     2    |  Football     |     1
     3    |  PlayStation  |     1
     4    |  Boxing       |     2

Third (Balances_Table)

Balance_ID| Balance | Master_ID
-------------------------------
     1    |  2000   |     1
     2    |  3000   |     1
     3    |  1500   |     1
     4    |  2500   |     2

and i have function called (fn_calc_balance_for_user) that used to make some calculation on Third Table (Balances) and use some Parameters Bathed throw query from First Table .

and i have sql query that render data from Master_Table + Activites_Table and Balances_Table like that :

select dbo.Master_Table.Master_ID,
       dbo.Master_Table.Name,
       Activites_Table.Activity_Name ,
      (select dbo.fn_calc_balance_for_user(dbo.Master_Table.Master_ID) as EXP1) as Total_Balance
From   dbo.Master_Table INNER JOIN dbo.Activites_Table
       ON 
       dbo.Master_Table.Master_ID = dbo.Activites_Table.Master_ID

The Query Result Will be that :

Master_ID | Name | Activity_Name | Total_Balance
------------------------------------------------
    1     | John |   Swimming    |    6500
    1     | John |   Football    |    6500
    1     | John |   PlayStation |    6500
    1     | Max  |   Boxing      |    2500

as this Point every thing work but attention on function callecd (fn_calc_balance_for_user) that will be called on every row on that query and thins function is just for example and the real function make more complicated calculations and its not good for performances to do that and my Crystal Report will show that results as it :

_______________________________________________________________

ID      : (1)
Name    : (John)
Balance : (6500)

Activities :
--------------------
- Swimming
- Football
- PlayStation
_______________________________________________________________

ID      : (2)
Name    : (Max)
Balance : (2500)

Activities :
--------------------
- Boxing
______________________________________________________________

my Question is : – how can i make (fn_calc_balance_for_user) called once for every Master_ID and not on all rows i mean when u see the results u will be note that john records as 3 rows and every row on that function called i need just for every user like john,max etc … exeucte the function once and not on every rows to save performances , some one said to me u must use grouping iam ok for that and i use it but inside my crystal report by make grouping by Master_ID but on the original sql how can i target the data and not repeat calling (fn_calc_balance_for_user) every time we have row on activites_table

This is For Discussion Boys …

thanks for every body .

Answer

Try this:

select dbo.Master_Table.Master_ID,
       dbo.Master_Table.Name,
       Activites_Table.Activity_Name ,
       t.Total_Balance
From   dbo.Master_Table INNER JOIN dbo.Activites_Table
       ON 
       dbo.Master_Table.Master_ID = dbo.Activites_Table.Master_ID

       inner join (select Master_ID, 
                          fn_calc_balance_for_user(t.Master_ID) as Total_Balance
                     from (select distinct Master_ID
                             from Master_Table)t)t
       on t.Master_ID = dbo.Activites_Table.Master_ID;

The goal is to find unique Master_ID’s and call fn_calc_balance_for_user once for each of them. It can be easily achieved with a separate query like above.