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

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.
jstucklex@attglobal.net
==================

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

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!

On Feb 12, 2:51 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
wrote:
> 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!



CREATE TABLE `TReferal` (
`referal_id` bigint(20) NOT NULL default '0',
`amount` varchar(15) default NULL,
`refered_Date` datetime default NULL,
`by_Whom` varchar(30) default NULL,
`position_id` bigint(20) default NULL,
`to_email` varchar(100) default NULL,
`referalParent_id` varchar(10) default NULL,
PRIMARY KEY (`referal_id`),
KEY `FKB9C829B711238ACC` (`position_id`),
CONSTRAINT `FKB9C829B711238ACC` FOREIGN KEY (`position_id`)
REFERENCES `TPosition` (`job_id`)
);


CREATE TABLE `TReferalApplied` (
`referalApplied_id` bigint(20) NOT NULL default '0',
`joined_Date` datetime default NULL,
`referal_id` bigint(20) default NULL,
`status` varchar(30) default NULL,
`user_Name` varchar(30) default NULL,
PRIMARY KEY (`referalApplied_id`),
KEY `FKBB4F92C6BBAB99E8` (`referal_id`),
CONSTRAINT `FKBB4F92C6BBAB99E8` FOREIGN KEY (`referal_id`)
REFERENCES `TReferal` (`referal_id`)
);

insert into TReferal values(18,"1000",2007-02-10-00-00,"venkat",
1,sh@sh.com,null,null,120);
insert into TReferal values(19,null,2007-02-10-00-00,"appli5",
1,shal@sh.com,18,18,321);
insert into TReferal values(20,null,2007-02-10-00-00,"appli1",
1,xyz@h.com,19,19,325);
insert into TReferal values(21,null,2007-02-10-00-00,"appli2",
1,xyz@h.com,20,20,328);

insert into TReferalApplied
values(1,2007-02-10-00-00,20,"joined","appli1");
insert into TReferalApplied
values(2,2007-02-10-00-00,21,"joined","appli2");




when ever we pass referral_id it should get all the candidates
referal_ids who are having the status ="joined" and the amount how
much this refered person going to get (this amount is divided equally
means if there is referal chain depth is 2 means it divided by 2)

in this ex:

when we pass referal_id=19 then should get both candidates details
like

referal_id as 20 and amount 500
referal_id as 21 and amount 333

when we pass referal_id=20 then should get
only
referal_id as 21 and amount 333


to get this data how to write query in mysql DB


i think now u understood.

pls reply back

thanQ