MS SQL 8 Query

Posted on 2011-04-21
Last Modified: 2012-06-21
I don't know if MSSQL 8 will do this, but here is the issue.  I am building a membership system that needs to track up to 3 levels on who recommended whom.  Here is a quick example:
Person1 signs up Person2
Person2 signs up Person3
Person3 signs up Person4
In a table I need to show that not only Person1 is resposible for Person2, but also Person3,Person4
It also needs to show that Person2 is responsible for Person3&Person4 as well as
Person3 is responsible for Person4.

I am not sure if a query will do this, any assistance would be appreciated.  By the way this is a webbased coldfusion site.

Question by:rbm1tch3ll
    LVL 40

    Expert Comment

    Provide your data representation in the table with expected result?
    LVL 22

    Accepted Solution

    If you set up your table so that it has a column for RecommendedBy and you let that column contain a NULL if the member is at the first level (i.e. not recommended up by anyone), then you can do this.  It will involve what is referred to as a Self-Join on the Membership table.

    The basics of the Self Join are shown below:
    SELECT  M.MemberName
    INNER JOIN Membership M2
    ON M.MemberName = M2.RecommendedBy;

    Open in new window

    Not having the data to work with, I think the following might work:
    WITH Recommendations AS
        (SELECT M1.MemberName
        ON  M1.MemberName = M2.RecommendedBy
    SELECT R1.MemberName
    FROM Recommendations R1
    LEFT OUTER JOIN Recommendations R2
      ON  R2.RecommendedBy = R1.MemberName
    LEFT OUTER JOIN Recommendations R3
      ON  R3.RecommendedBy = R2.MemberName;

    Open in new window


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
    In this article we have discussed about the OS X EI Capitan and how to fix Wi-Fi issue in OS X El Capitan. We have explained how to delete system level preferences and create a new Wi-Fi location to resolve Wi-Fi issue.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now