?
Solved

MS SQL 8 Query

Posted on 2011-04-21
2
Medium Priority
?
241 Views
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.



0
Comment
Question by:rbm1tch3ll
2 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35445378
Provide your data representation in the table with expected result?
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 2000 total points
ID: 35445409
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
       ,M2.RecommendedBY
       ,M2.MemberName
FROM MEMBERSHIP M
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
      ,M2.RecommendedBY
    FROM MEMBERSHIP M1
    LEFT OUTER JOIN MEMBERSHIP M2
    ON  M1.MemberName = M2.RecommendedBy
    )
SELECT R1.MemberName
             ,R2.MemberName
             ,R3.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

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
A lot of new and distinct gadgets are making their appearance every other day. The latest gadget that has wooed the attention of all gadget lovers and non gadget lovers alike is the Smartwatch. This tiny gadget is capable of offering live access to …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

609 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