sql query for network marketing

Hi Jahi

Please give your requirement little clear

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Hi...

You should describe more to clear your problem.

Best Regards,
Sutotpal

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

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


Thus , member's sponsor and sponsor's sponsor and sponsor's sponsor etc.......

Plz help me

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Your query needs to be retrived based on which condition?

Thanks
Karthik
www.f5Debug.net

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Hi Jahi

Try this....

SELECT 'sponson of ' + memberid + ' , answer is : ' + sponsor  FROM details

sponson of Q0001 , answer is : Q0000

sponson of Q0003 , answer is : Q0001

sponson of Q0004 , answer is : Q0000

sponson of Q0005 , answer is : Q0001

sponson of Q0006 , answer is : Q0003

sponson of Q0007 , answer is : Q0002

sponson of Q0002 , answer is : Q0000

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

I think Ur expecting like this..

for(condition)
{
select sponser from details where memberid=@memberid
@memberid=sponser
}

Sathish Kumar S

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Hi, Karthik sir....

At the time of confirmation of registered member, certain amount of commission will get direct sponsor's of this member and sponsor of this direct sponsor and sponsor of this sponsor and etc.. till administrator.............................

for that purpose , to know the sponsor list.

Either query for retrieval of this sponsors list or insert query to particular sponsors commission.

Thanks in advance

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Here is the complete code:

Use Stored procedure

BEGIN
WHILE @memberid> null
BEGIN
select sponser from details where memberid=@memberid
set @memberid=sponser
END

RETURN
END

Hope this helps U

Sathish Kumar S

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Thanks Jahi,

You can refere to pandians response its staright forward on what you looking for

Thanks
Karthik
www.f5Debug.net

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Jahi,

If ur prob has been resolved kindly post the answer...

Sathish Kumar S

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

----------------.aspx page

SqlConnection con4 = new SqlConnection(WebConfigurationManager.ConnectionStrings["gmi_con"].ConnectionString);

SqlCommand cmd4 = new SqlCommand("sponsorcommission", con4);
cmd4.Connection = con4;
cmd4.CommandType = CommandType.StoredProcedure;
con4.Open();
SqlParameter mem = new SqlParameter("member", lbl_memberid1.Text);

cmd4.Parameters.Add(mem);
GridView3.DataSource = cmd4.ExecuteReader(CommandBehavior.CloseConnection);
GridView3.DataBind();







------------------------stored procedure

ALTER PROCEDURE dbo.sponsorcommission

@member varchar(50)

As

BEGIN

WHILE @member> null
BEGIN
DECLARE @sponsor varchar(50)
select @sponsor=sponsor from tree where member=@member;
set @member=@sponsor;
END

RETURN
END

-----------------------------------------------------------------------------------------------------------------------------------------

This is the code that i wrote. But i did'nt get any answer.

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

at sp right client and move to execute and then input the member id and let me know if sp not works...

Sathish Kumar S

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Please ,clear it. don't understand that your suggession.

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Do you need to know the process of the stored procedure?

Thanks
Karthik
www.f5Debug.net

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Hi Jahi

Are you looking like this...?

SELECT 'sponsor of ' + sponsor + ' , answer is : ' + LEFT(Result,LEN(Result)-1) Result FROM 

(

SELECT O.sponsor,

(SELECT I.memberid + ',' FROM details I WHERE I.sponsor = O.sponsor FOR XML PATH('')) Result

FROM details O GROUP BY O.sponsor

)AS X

sponsor of Q0000 , answer is : Q0001,Q0004,Q0002

sponsor of Q0001 , answer is : Q0003,Q0005

sponsor of Q0002 , answer is : Q0007

sponsor of Q0003 , answer is : Q0006

* Kindly choose the appropriate Category, This discussion should be there under "SQL Server", Because, somebody will respond ontime based on the Category..

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Sorry sir, Not for that.
Above query's answer is members list of a specified sponsor. But not for that.

i want answer like this:
A member' sponsor and then sponsor of that sponsor and then sponsor of that sponsor,..etc till Q00000.

Because Q0000 is the administrator.

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Basically do you want to sort it down from the top sponsor to the administrator?

Thanks
Karthik
www.f5Debug.net

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

hi sir,

I explain it very clearly.

at the time of registration of a member, a certain amount will get sponsor of that member. And not only the direct sponsor of the member but also the sponsor of that sponsor and sponsor of that sponsor and that ends with administrator. but this list will not contain all members of this site . Only member's sponsor and sponsor ,etc.. till administrator.

Thanks
Jaheena

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

I have got the correct query for my problem.


string TEMPID = "";
bool MYFLAG = false;
SqlConnection con3 = new SqlConnection(WebConfigurationManager.ConnectionStrings["newconnection"].ConnectionString);
con3.Open();
TEMPID = labelmemberid.Text;
while (!MYFLAG)
{
SqlCommand CMD2 = new SqlCommand("SELECT SPONSOR FROM TREE WHERE MEMBER='" + TEMPID + "'", con3);
RDR = CMD2.ExecuteReader();
while (RDR.Read())
{
//Response.Write("<script> alert('" + RDR[0].ToString() + "'); </script>");
ListBox1.Items.Add(RDR[0].ToString());
TEMPID = RDR[0].ToString();
if (RDR[0].ToString() == "GMIWEB0")
MYFLAG = true;
}
RDR.Close();

}
con3.Close();


Thanks for helping me to all.

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Thats good!!!!

Thanks
Karthik
www.f5Debug.net

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Hey, I am looking for some network marketing company who can promote my health product. I need at least five business promoter in the Asia for promoting they should have good face value and contacts too in their region. Profit unbelievable.

http://maxtransformation.com/

Jahi, if this helps please login to Mark As Answer. | Alert Moderator

Jahi,

I don't agree with the solution you have adopted. Contacting database from .NET code many times, is not a good idea for a small thing.
I will rather suggest that you should have written a stored procedure for this which accepts input as UserId and provides us all of this manager(s) until the root of manager's tree.

Have a look on below query that should help you...
DECLARE @UserId INT
DECLARE @ManagerCount INT
DECLARE @tblTemp TABLE(Id INT IDENTITY(1,1), UserId INT, UserName VARCHAR(50))
SET @UserId = 6
SET @ManagerCount = -1

WHILE(@UserId > 0)
BEGIN
INSERT INTO @tblTemp
SELECT UserId, [Name] FROM tblChainBusiness WHERE UserId = @UserId

SELECT @UserId = ISNULL(ParentId, 0) FROM tblChainBusiness WHERE UserId = @UserId
SET @ManagerCount = @ManagerCount + 1
END

SELECT * FROM @tblTemp ORDER BY Id DESC
SELECT @ManagerCount [Managers]

Consider you are having a table where UserId and his ParentId both are in the same table.
This query will give you output as highest manager on top and final child (User) on last row.
In addition I have taken an additional parameter for manager count which will help you to divide their commission in percentage. If you don't need that remove.
I have tested this query for a table which has 89000 rows and maximum 23 level of tree in relationship.
Give it a try, it is quick enough in execution. :)

--
Sojanya Tripathi
www.dotnetguidance.com

Jahi, if this helps please login to Mark As Answer. | Alert Moderator