loop for string

Question

I have a problem that is I trying to retrieve data from database to the extent. I explain very Clearly I have a table named: details.mdf Fields: sponsor nameofmember memberid dateofjoining Q0000 ashi Q0001 12-20-2009 Q0000 sfgsf Q0002 13-20-2009 Q0001 dghdh Q0003 12-20-2009 Q0000 tyety Q0004 12-20-2009 Q0001 gdhdgh Q0005 12-20-2009 Q0003 gdh Q0006 12-20-2009 Q0002 ghf Q0007 12-20-2009 what can i do for result like this below: sponsor of Q0006  answer is : Q0003 and then who is the sponsor of Q0003  answer is: Q0001 etc...... Thus dynamically retrieve the datas from database . I think for this we can use loop. but I dont know what is the correct code for this purpose because of all the values are string . Please help me to retrieve datas in this manner? thanks in advance

Suthish Nair · Answer

select distinct 'sponser of memberid ' + b.memberid + ' is ' + a.sponsor from dbo.details a left join dbo.details b on a.sponsor = b.sponsor

Krishna Garad · Answer

try this select e1.nameofmember 'Sponcer'e2.nameofmember 'Sponcer2' from tree e1 join tree e2 on e1.sponsor=e2.memberid

jaheena jamal · Answer

please help me....sir plz

jaheena jamal · Answer

I am so sorry to disturb you more n more . This is the query: SELECT e1.sponsor AS 'SPONSOR' e2.member AS 'MEMBER' FROM tree AS e1 INNER JOIN tree AS e2 ON e1.member = e2.sponsor After execution of this query the results is only direct sponsor. I don't know to add another one join . Please help me.

Krishna Garad · Answer

you can add one more join condition and can get the result. ans in select list only add sponcers's sponcer.

jaheena jamal · Answer

In that example  result is only direct sponsors. but I want sponsor's sponsor and then sponsor's sponsor etc.... till that ends into the Administrator.

Krishna Garad · Answer

Refer the link it's excellent example like your query ReferThis

jaheena jamal · Answer

Please explain. I don't know what is the query for that.

Krishna Garad · Answer

use self join.