self join in a table

Question

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

Please clarify your expected output. – JNK Sep 27 '11 at 17:58 — JNK, Sep 27 '11 at 17:58

score 6 · Accepted Answer · answered Sep 27 '11 at 17:57

6

Unless I'm missing something:

SELECT *
FROM Customer
WHERE ReferredBy IS NOT NULL

answered Sep 27 '11 at 17:57

JNK

63,321
15
122
138

I think what the OP meant was creating a relationship between `CustID` and `ReferredBy`. – Sep 28 '11 at 00:51
@Surfer513 - well that's not what he said, and this matches his desired output. – JNK Sep 28 '11 at 10:52

Mahmoud Gamal · Answer 2 · 2013-01-03T04:18:07.587

1

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;

edited Jan 03 '13 at 04:18

answered Sep 27 '11 at 18:26

Mahmoud Gamal

78,257
17
139
164

Won't work as written, since you are `JOIN`ing on `NAME` and that's not what's used in the relation. I think it should be `INNER JOIN Ctes cs ON c.ReferedBy = cs.Id` – JNK Sep 27 '11 at 19:55
cs.CustomerName is selected as RefCustomer in the union all portion – Mahmoud Gamal Sep 27 '11 at 20:35
You are correct. I'd say run it against a `#temp` table to test it – JNK Sep 27 '11 at 20:42

self join in a table

2 Answers2