CODEWITHSUNDEEP
sqlunion
rawatdeepesh
rawatdeepesh

Reputation: 584

SQL query to find the friends in a table

I have two tables, Users and Friends. The tables look like:

USERS
|  ID   |  Name   | 
|  1    |  Sam    | 
|  2    |  Harry  |         
|  3    |  Vivi   |
|  4    | sabrina |

FRIENDS
| UId   | FriendID| 
|  1    |    2    |    
|  2    |    3    |  
|  4    |    1    | 
|  5    |    4    | 
|  1    |    3    |

I need to find the names of all the friends for Sam. I tried doing the same using a Union in an SQL query, but I couldn't get the desired output. Can I possibly get the required output doing the same?

Upvotes: 1

Views: 670

Answers (2)

Sharma Dhananjay
Sharma Dhananjay

Reputation: 437

declare
@answer nvarchar(max)='{'
select @answer=@answer+u1.Name+',' from USERS u
inner join FRIENDS f on f.UId=u.ID
inner join USERS u1 on u1.ID=f.FriendID
where u.ID=<what ever you want> //1 or 2 or 3 or 4

set @answer=SUBSTRING(@answer,0,len(@answer)-1)+'}'
select @answer

Upvotes: 6

keltar
keltar

Reputation: 18399

select u.name from users
join friends f on users.id=f.uid
join users u on u.id=f.friendid
where users.name='Sam';

Upvotes: 1

Related Questions