On Feb 10, 6:56 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> sh wrote:
> > i have two tables one is Referal table 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
>
> I'm not sure I understand your question.
>
> It would be much easier to understand what you want if we had the table
> definitions, some sample data and the results you want to see.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================



this the TREferal table:

mysql> select * from TReferal;
+------------+--------+---------------------+---------+------
+-------------+----------------------------------+-----------
+------------------+---------+
| referal_id | amount | refered_Date | by_Whom | position_id
| to_email | parent_id | referalParent_id |
user_id |
+------------+--------+---------------------+---------+------
+-------------+----------------------------------+-----------
+------------------+---------+
| 18 | 1000 | 2007-02-06 13:10:24 | venkat | 1 |
shailaja.devapooja@xyz.com | NULL | NULL | NULL |
| 19 | NULL | 2007-02-06 13:11:30 | appli5 | 1 |
shailajabtech@gmail.com | 18 | 18 |
331 |
| 20 | NULL | 2007-02-06 13:16:19 | appli1 | 1 |
devapoojashailaja@yahoo.com | 19 | 19 | 328
|
| 21 | NULL | 2007-02-06 16:42:53 | appli2 | 1 |
manonita.pattanaik@xyzcom | 20 | 20 | 329 |
| 22 | NULL | 2007-02-06 17:00:28 | appli2 | 1 |
shailu@gmail.com | 20 | 20 |
329 |
+------------+--------+---------------------+---------+------
+-------------+----


this is TReferalApplied table

select * from TReferalApplied;
+-------------------+---------------------+------------+---------
+---------+
| referalApplied_id | joined_Date | referal_id | status |
user_id |
+-------------------+---------------------+------------+---------
+---------+
| 1 | 2007-02-06 00:00:00 | 19 | Joined |
328 |
| 2 | 2007-02-06 00:00:00 | 20 | Applied |
329 |
+-------------------+---------------------+------------+---------
+---------+


these are two tables.


taking 2 tables into consideration ,
for perticular referal_id in want to show all the Joined Candidates
Referal_id's (means in that parent child relations only) and their
amounts how much they going to get.
following that child parent relation ship.

EX: in the above table parent is referal_id=18,, assume amount is
1000

if i want to get details for referal_id=19 then,
it should show: joined candidates list (directly/indirectly) with
amounts

for this example: it should show referal_id 20 like this


please if u know the solution , reply back.
its very urgent