SQL select recursively

Question

I have the following table

UserID  | User knows
--------------------
a       |          b
a       |          c
c       |          e
c       |          a
d       |          f
d       |          g

I want to get a list in order to know which users are connected (also via each others).

e.g for user (a) the list should contain: b,c,e,a

Is there a way to do it in sql or do I have to code something?

Answer 1

If you are using SQL Server 2005 or newer try something like this:

WITH userCTE
AS ( 

    SELECT UserKnows
    FROM users
    WHERE UserId = 'a'
    UNION ALL
    SELECT UserKnows
    FROM users
    INNER JOIN userCTE 
     ON users.UserId= userCTE.UserKnows
    WHERE users.UserId != 'a'
)
SELECT *
FROM userCTE

You can see it in action here: http://sqlfiddle.com/#!3/d41d8/1832/0