CODEWITHSUNDEEP
sql
TintinSnowy
TintinSnowy

Reputation: 43

Find all the FriendId where ID=1 using join only

Table:

ID. FriendId
1    2
2    3
1    4
4    5
5    6

Result:

2
3
4
5

Need friendId till level 2 only i.e. direct friend and friend of friend.

I was asked this question in an interview. I gave following queries using nested query and view but its incorrect. The interviewer asked to use "JOIN" only.

select friendId from table where Id in (Select friendId from 
table where id=1);

with t1 as (Select friendId from table where id=1)
select friendId from table join t1 on table.Id= t1.friendId ;

Upvotes: 0

Views: 59

Answers (1)

jarlh
jarlh

Reputation: 44786

Non-cte query to get "friendId till level 2 only":

select FriendId
from tablename
where ID = 1  -- direct friend
   or ID in (select FriendId from tablename where ID = 1) -- friend of a friend

Or, using JOIN:

select distinct t1.FriendId
from tablename t1
join tablename t2
   on t1.ID = 1  -- direct friend
   or t2.id = 1 and t2.FriendId = t1.ID -- friend of a friend

Upvotes: 1

Related Questions