MySQL stored function with table

Question

I've the following result returned by a mysql function named GetFamilyTree

565,586,579,587,596,591,594,595

and another table named salary contains the following

+------+-------+
| uid  | sal   |
+------+-------+
|  565 | 10000 |
|  568 | 20000 |
|  587 | 15000 |
|  595 |  7000 |
|  596 | 40000 |
+------+-------+

I need the total salary of all the members.

I do not want to use a temporary table.

I've tried the following so far without success

select sum(sal) from salary where uid in (select GetFamilyTree('550'))

Please tell me how to do it.

Answer 1

When a set of values are in the form of a CSV, then you need to use find_in_set. Values must be separated only by a comma and nothing else.

Try this:

select 
  sum(s.sal) total_salary
from 
  salary s, 
  (select GetFamilyTree('550') as ftree) as t 
where 
  s.uid find_in_set( s.uid, t.free )

Refer to:

• SQL Fiddle Example
  I have used return string of your function GetFamilyTree('550') for demo.
• FIND_IN_SET(str,strlist)
  Return the index position of the first argument within the second argument.