On 12 Feb, 06:31, "sh" <shailajabt...@gmail.com> wrote:
> 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.devapo...@xyz.com | NULL | NULL | NULL |
> | 19 | NULL | 2007-02-06 13:11:30 | appli5 | 1 |
> shailajabt...@gmail.com | 18 | 18 |
> 331 |
> | 20 | NULL | 2007-02-06 13:16:19 | appli1 | 1 |
> devapoojashail...@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 |
> sha...@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- Hide quoted text -
>
> - Show quoted text -

Jerry asked if you would post the table definitions and some sample
data.
You have not posted the definitions and the sample data that you have
posted would have to be manually typed into a database to supply
something that we could play with.

If this is as urgent as you suggest, then it might be a good idea to
help us help you by posting what is requested.

An export of the table structures as CREATE TABLE sattements and a few
rows of sample data as INSERTS will make it far easier to help you.

Add some samples of what you expect to have as output and we'd have
the full house!