How to select a user from A when it has a match in B

Question

I have 2 tables like this:

TableA

+----+------- --+
| id | name     |
+----+----------+
|  1 | Max      |
|  2 | Susan    |
|  3 | Tom      |
+----+----------+

TableB

+----+----------+----------+
| id | fromUser | toUser   |
+----+----------+----------+
|  1 | 1        | 3        |
|  2 | 1        | 2        |
|  3 | 3        | 1        |
+----+----------+----------+

Now, I want to have Tom as a result because Max gives to Tom and Tom gives to Max.

I know Max's ID.

I tried:

 select a.*, b.fromUser from TableA a 
 INNER JOIN TableB b 
    ON b.fromUser = a.id OR b.toUser = a.id
    WHERE b.fromUser = 1 AND a.id =! 1 

and a lot of variations of this query. But I either got the wrong results or too many!

Answer 1

You need 1 row from TableA as a result, right?
You can use EXISTS twice:

select a.*
from TableA a
where 
  exists (
    select 1 from TableB 
    where fromUser = a.id and toUser = 1
  )
  and
  exists (
    select 1 from TableB 
    where fromUser = 1 and toUser = a.id
  )

Answer 2

You need two joins to bring in the user names. But then you also need to find users where the opposite relation exists.

So:

select ato.name
from tableB b join
     tableA afr
     on b.fromuser = afr.id join
     tableA ato
     on b.touser = afr.id
where exists (select 1
              from tableB b2
              where b2.fromuser = b.touser and b2.touser = b.fromuser
             ) and
      afr.name = 'Max';

If you know Max's id, you don't need both joins;

select ato.name
from tableB b join
     tableA ato
     on b.touser = afr.id
where exists (select 1
              from tableB b2
              where b2.fromuser = b.touser and b2.touser = b.fromuser
             ) and
      b.fromuser = 1;