self join in a table

 CustID       Name         ReferredBy
 1         Neeta Sayam  
 2         Dolly Dilly         1
 3         Meena Kimi          2

Find the names of all customers who are referred by others.

the output should be dolly dilly and 开发者_JAVA百科meena kimi.

I have succesfully found out for customers who have reffred others by query

 SELECT c1.name FROM Customer c1 JOIN Customer c2 ON c1.custid=c2. ReferredBy

Unless I'm missing something:

SELECT *
FROM Customer
WHERE ReferredBy IS NOT NULL

There are a lot of ways to achieve what you want, but one interesting is using CTE because it allows you to get reference by level, for example in your case level zero will be Neeta syam that has no reference, level one is dolly dilly and meena kimi. The following query will return dolly dilly and meena kimi which are in the level one specified by where reference = 1 like so:

WITH CTEs (Id, CustomerName, Reference, RefCustomer)
AS
(
  SELECT 
    Id,
    Name,
    0 As Reference, 
    CONVERT(VARCHAR(length), 'No Reference') AS RefCustomer 
  FROM Customers 
  WHERE ReferredBy IS NULL
  UNION ALL
  SELECT
    c.CustId,
    c.Name, 
    cs.Reference + 1, 
    cs.CustomerName 
  FROM Customers c 
  INNER JOIN Ctes cs ON c.ReferedBy = cs.Id
)
SELECT CustomerName, RefCustomer 
FROM Ctes 
WHERE Reference = 1;