Mr. Developer Mr. Developer - 4 months ago 9
SQL Question

How to get those records whose following each other in mysqli?

I'm tyring to fetch those records (

users
) whose following each other. I have following table.

id | follower | following
--+-----------+----------
1 | a | c
2 | b | a
3 | a | g
4 | g | b
5 | c | a


What I'm trying ?



I'm trying simple like operator for the task.

SELECT * FROM `table` WHERE follower LIKE '%a%' OR following LIKE '%a%'


Result



id | follower | following
--+-----------+----------
1 | a | c
2 | b | a
3 | a | g
4 | c | a


Above query is working fine as per the logic and
like
operator, but i wanted to fetch those records whose follow each other. Means in above result i need only first and last record, because
a
and
c
are following each other.

I need



id | follower | following
--+-----------+----------
1 | a | c
2 | c | a


Can anyone guide me how can i fetch this kind of record, I would like appreciate if someone help me. Thank You

Answer
  select t1.* 
     from thetable t1 
     join thetable t2 
     on t1.follower = t2.following and  t2.follower = t1.following

demo

UPDATE:

If you interest of user 'a'

 select t1.* 
    from t t1 
    join t t2 
    on t1.follower = t2.following and  t2.follower = t1.following 
    where 'a' in (t1.follower,t1.following,t2.follower,t2.following) 

demo