Professional Web Applications Themes

how to write mysql recursive query for following condition - MySQL

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 ...

  1. #1

    Default how to write mysql recursive query for following condition


    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 Guest

  2. #2

    Default Re: how to write mysql recursive query for following condition

    sh wrote: 

    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.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: how to write mysql recursive query for following condition

    On Feb 10, 6:56 pm, Jerry Stuckle <net> wrote: 









    >
    > 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.
    > net
    > ==================[/ref]



    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 |
    com | NULL | NULL | NULL |
    | 19 | NULL | 2007-02-06 13:11:30 | appli5 | 1 |
    com | 18 | 18 |
    331 |
    | 20 | NULL | 2007-02-06 13:16:19 | appli1 | 1 |
    com | 19 | 19 | 328
    |
    | 21 | NULL | 2007-02-06 16:42:53 | appli2 | 1 |
    manonita.pattanaikxyzcom | 20 | 20 | 329 |
    | 22 | NULL | 2007-02-06 17:00:28 | appli2 | 1 |
    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


    sh Guest

  4. #4

    Default Re: how to write mysql recursive query for following condition

    On 12 Feb, 06:31, "sh" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > 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 |
    > com | NULL | NULL | NULL |
    > | 19 | NULL | 2007-02-06 13:11:30 | appli5 | 1 |
    > com | 18 | 18 |
    > 331 |
    > | 20 | NULL | 2007-02-06 13:16:19 | appli1 | 1 |
    > com | 19 | 19 | 328
    > |
    > | 21 | NULL | 2007-02-06 16:42:53 | appli2 | 1 |
    > manonita.pattanaikxyzcom | 20 | 20 | 329 |
    > | 22 | NULL | 2007-02-06 17:00:28 | appli2 | 1 |
    > 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 -[/ref]

    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!

    Captain Guest

  5. #5

    Default Re: how to write mysql recursive query for following condition

    On Feb 12, 2:51 pm, "Captain Paralytic" <com>
    wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]











    >
    > 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![/ref]



    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,com,null,null,120);
    insert into TReferal values(19,null,2007-02-10-00-00,"appli5",
    1,com,18,18,321);
    insert into TReferal values(20,null,2007-02-10-00-00,"appli1",
    1,xyzh.com,19,19,325);
    insert into TReferal values(21,null,2007-02-10-00-00,"appli2",
    1,xyzh.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







    Manonita Guest

Similar Threads

  1. How to sort the Output of a recursive query?
    By InteractiveDialog in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 16th, 03:29 PM
  2. Help wth CF_MODULE and Recursive Query
    By don@orbandesign.com in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 22nd, 07:27 PM
  3. Recursive Query / Breadcrumb
    By popanot in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 16th, 02:44 PM
  4. Recursive query
    By Roberto C in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: September 17th, 12:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139