Hi Ramar,
If I have understood u right. You want the output from the below table to
be like
tablex
emp refby
kumar raj
raj ram
ram ragu
ragu rajesh
kumar was refered by raj, who was referred by ram, who was referred by ragu,
who was referred by rajesh
Try the below and see;
declare
var1 VARCHAR(20);
var2 VARCHAR(20);
var3 VARCHAR(20);
var4 VARCHAR(20);
var5 VARCHAR(20);
begin
var1=kumar;
loop
select refby into var2 from tablex where emp=var1;
select refby into var3 from tablex where emp=var2;
select refby into var4 from tablex where emp=var3;
select refby into var5 from tablex where emp=var4;
dbms_output.put_line
var1 || "was referred by" || var2 || "who was referred by" || var3
|| "who was referred by" || var4 || "who was referred by" || var5;
end loop;
end;
Regards,
Ashish
On Thu, Nov 27, 2008 at 9:27 AM, Ramar K <[EMAIL PROTECTED]> wrote:
> Dear all,
>
> I am doing my MCA project in LAMP .In my project ,I have one requirement to
> create multiple referral :my requirement is
>
> I have one table with cust_id ,cust_name,referal_name . I am using this
> query to create that table
> create table customers(cust_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY
> KEY ,cust_name VARCHAR(25) default NULL,referal_name VARCHAR(25) default
> NULL);
>
> insert into customers(cust_id,cust_name)values (1,'ram');
> insert into customers(cust_id,cust_name)values (2,'raj','ram');
> insert into customers(cust_id,cust_name)values (3,'kumar','raj');
> insert into customers(cust_id,cust_name)values (4,'ragu','kumar');
> insert into customers(cust_id,cust_name)values (5,'rajesh','ragu');
>
> It looks like this:
>
> cost_id cust_name referal_name
> 1 ram NULL
> 2 raj ram
> 3 kumar raj
> 4 ragu kumar
> 5 rajesh ragu
>
>
> I am not able to understand the logic behind the multiple refferel .what i
> want to do is i want upto 4 level referals
> i.e,My requirement is to display upto 4 revel referals.for example just
> take
> kumar in above table it will display
>
> ram <--------------- raj <---------- kumar---------> ragu
> -------------> rajesh
>
> i want to display grant father ,father ,sun and grand sun of kumar.
> kumar----->raj (kumar refered by raj)
> raj --------->ram (raj refered by ram)
> kumar------>ragu(ragu refered by kumar)
> ragu ------->rajesh (rajesh refered by ragu)
>
> This is one my requirement .I want to do this without using joint query . I
> don't have any idea how to select and display those information from above
> table .I am googled it but badly i am not able get any information .any
> one
> can help me please.I think it can achive using dinemic loop but i am not
> able to realise how will do it . please help me.
>
> Thanks for your time
>
> Rg
> K. Ramar.
> _______________________________________________
> To unsubscribe, email [EMAIL PROTECTED] with
> "unsubscribe <password> <address>"
> in the subject or body of the message.
> http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
>
_______________________________________________
To unsubscribe, email [EMAIL PROTECTED] with
"unsubscribe <password> <address>"
in the subject or body of the message.
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
