Description:
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
