SQL Server - Recursive Query to Return Distinct Email/Phone Relative to a Given Email

Question

Considering the following source data:

| Id | Email    | PhoneNumber |
+----+----------+-------------+
| 1  | p@q.com  | 1231231234  |
| 2  | a@b.com  | 9876543210  |
| 3  | x@y.com  | 1231231234  |
| 4  | x@y.com  | 5551231234  |
| 5  | l@m.com  | 5559876543  |
| 6  | c@d.com  | 9876543210  |
| 7  | p@q.com  | 5555555555  |
| 8  | y@z.com  | 5554443333  |
| 9  | e@f.com  | 5555555555  |

I need a recursive SQL statement that will take a starting email and search all rows that are related to the given email and associated phone numbers.

For example, if the initial search Email is x@y.com then the results should return

| Id | Email    | PhoneNumber |
+----+----------+-------------+
| 3  | x@y.com  | 1231231234  |
| 4  | x@y.com  | 5551231234  |
| 1  | p@q.com  | 1231231234  |
| 7  | p@q.com  | 5555555555  |
| 9  | e@f.com  | 5555555555  |

So,

x@y.com has 2 associated phone numbers ( 1231231234 & 5551231234 )
phone number 1231231234 also is associated to email p@q.com
p@q.com has 2 associated phone numbers ( 1231231234 & 5555555555 )
phone number 5555555555 also is associated to email e@f.com

Thanks in advance for your consideration.

=============================================================== I have tried different ways and most were running away and getting the error

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

The following is what I had before putting this question in...

DECLARE @SourceData TABLE (
    Id INT IDENTITY(1,1),
    Email VARCHAR(255),
    PhoneNumber VARCHAR(10)
)

INSERT INTO @SourceData ( Email, PhoneNumber )
VALUES 
    ('p@q.com', '1231231234'),
    ('a@b.com', '9876543210'),
    ('x@y.com', '1231231234'),
    ('x@y.com', '5551231234'),
    ('l@m.com', '5559876543'),
    ('c@d.com', '9876543210'),
    ('p@q.com', '5555555555'),
    ('y@z.com', '5554443333'),
    ('e@f.com', '5555555555')


DECLARE @inEmail VARCHAR( 255 ) = 'x@y.com';


;WITH cteEmailPhone AS (
    SELECT DISTINCT
        Email,
        PhoneNumber
    FROM @SourceData
    WHERE Email = @inEmail

    UNION ALL

    SELECT
        a.Email,
        a.PhoneNumber
    FROM cteEmailPhone a 
    JOIN @SourceData b ON b.PhoneNumber = a.PhoneNumber 
    WHERE a.PhoneNumber <> b.PhoneNumber

)
SELECT *
FROM cteEmailPhone;

You've told us what you "need" but what are you asking? What about what *you've* tried did work, or what about the problem are *you* have trouble with? What research did you perform and didn't understand? — Thom A, Dec 20 '19 at 17:01
This is a pretty typical recursive cte. There are thousands and thousands of examples of this all over the internet and hundreds just on SO. — Sean Lange, Dec 20 '19 at 17:17
This one is harder than the usual parent/child recursion because you need to keep track of all of the rows already visited. Otherwise you will tend to get caught in a loop going from, say, `'x@y.com'` to `'p@q.com'` and back endlessly. [This](https://stackoverflow.com/a/15081353/92546) answer demonstrates one way to avoid the trap. — HABO, Dec 20 '19 at 20:02
Thanks all, I modified the question to include the sql I was running that was not erroring but also not returning what I expected. @HABO, Thanks, I am reading that post now. — smackalack, Dec 20 '19 at 20:25

score 0 · Accepted Answer · answered Dec 21 '19 at 03:03

The following common table expression (CTE) recursively processes the rows to get the desired results. The embedded comments explains the steps. It avoids getting trapped in a cycle by tracking the email addresses already processed and not processing them again.

with PhoneNumbers as (
  -- Get all of the phone numbers associated with the original email address.
  select Id, Email, PhoneNumber,
    -- Keep track of the email addresses we've already processed.
    Cast( Email as VarChar(4096) ) as ProcessedEmailAddresses
    from @SourceData
    where Email = @inEmail
  union all
  -- Get all of the phone numbers associated with the new email addresses.
  select SD.Id, SD.Email, SD.PhoneNumber,
    Cast( PN.ProcessedEmailAddresses + '|' + SD.Email as VarChar(4096) )
    from PhoneNumbers as PN inner join
      @SourceData as SD on
        -- The email address is associated with one of the phone numbers added in the prior pass and ...
        SD.Email in ( select iSD.Email from @SourceData as iSD where iSD.PhoneNumber = PN.PhoneNumber ) and
        -- ... the email address is one we haven't already processed.
        '|' + PN.ProcessedEmailAddresses + '|' not like '%|' + SD.Email + '|%'
  )
  select Id, Email, PhoneNumber, ProcessedEmailAddresses
    from PhoneNumbers;

Thanks for the clear question and sample data statements.

That is fantastic work HABO. And the descriptions really provide understanding. Thanks for your time and effort — smackalack, Dec 22 '19 at 04:31
For anyone interested, here is a [SQL Fiddle link](http://sqlfiddle.com/#!18/bd816/19/0) for the solution provided by HABO... — smackalack, Dec 23 '19 at 18:16

SQL Server - Recursive Query to Return Distinct Email/Phone Relative to a Given Email

1 Answers1