Bug #26251 how to write a recursive query to get the bosses (referrals)of that employer
Submitted: 10 Feb 2007 8:38 Modified: 11 Feb 2007 9:08
Reporter: devapooja shailaja Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:Linux (linux)
Assigned to: CPU Architecture:Any

[10 Feb 2007 8:38] devapooja shailaja
i have  two tables Referal that holds info on the referal_id,amount,position_id,referalParent_id,etc and ReferalApplied table contains referalApplied id ,status,and referal_id which is pointing to the Referal.referal_id.

here am implementing the Referral process code.
when ever that referal send a referal mail to the JobApplicant say A, that data stored in the first table. for this id am maintaing referalparent_id as the id of the referal who has sent mail to him,and only for first time referal mail am adding the amount in the DB.
if this A is sending referal mail to some other jobApplicant say B, now also data will be stored in the first table.for this referalParent_id as referal_id of the A.
and soon
if this  B is applied for the position then this data am storing in the second table  status as Applied , when ever he Joined in that poisition then am changing status as Joined.

now wt i have to do is ,

for perticular referal_id , i have to show  all the list of Joined candidates Referal_id s    (directly/indirectly) and the amount he is going to get

to get the above result how to write a query?

(Directly: candidate who have joined for that position, his  immediate referalParent_id is equal to the referal_id of that candidate

Indirectly: candidate who joined for that position , his immediate referalParent_id is not equal to the referal_id of that candidate but he should be the child of that candidate.)

amount : first time  i have entered amount  as 10000 then it can be equally  divided to all the childs until one of candidate get joined for that position.

pls reply back
its very urgent.
if u dont understand my language ,let me know i wil explain once again.

Thanks in advance

How to repeat:

how to write a query inorder to get the above mentioned result
[11 Feb 2007 9:08] Hartmut Holzgraefe
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.