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.

Ravinder Reddy · Accepted Answer · 2014-03-13 14:25:38Z

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.

answered Mar 13, 2014 at 14:25

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL stored function with table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related