Parthapratim Neog Parthapratim Neog - 5 months ago 15
MySQL Question

Self joining doesn't help here. What other approach can I use?

I am stuck with this problem. Consider the following table. I only know the value A(i.e. I can use something like

SELECT * from table WHERE user_one = A
). I tried doing a self join, but that didn't help.

Given table

+----------+-----------+---------+
| USER_ONE | USER_TWO | STATUS |
+----------+-----------+---------+
| | | |
| A | B | 0 |
| | | |
| B | A | 1 |
| | | |
| A | C | 1 |
| | | |
| C | A | 1 |
| | | |
| D | A | 1 |
| | | |
| A | E | 0 |
+----------+-----------+---------+


My desired result needs to be the following. Imagine
user_one
is following
user_two
if status is 1.Status 0 means, the
user_one
was previously following
user_two
, but now he unfollowed
user_two
. I need the users Who are following "A". Notice that I don't want, the rows where they are both following each other like
(A -> B)
and
(B -> A)
both has Status 1. So the question to the following response would be something like, "Find me people following A, but A is not following them", makes sense? A little help would be appreciated.

Desired Rows

+----------+-----------+---------+
| USER_ONE | USER_TWO | STATUS |
+----------+-----------+---------+
| | | |
| B | A | 1 |
| | | |
| D | A | 1 |
+----------+-----------+---------+

Answer

This should work:

Using count(*)

select
    t1.user_one,
    t1.user_two,
    t1.status
from
    table t1
where
    t1.status = 1 and
    -- t1.user_two = 'A' and -- If looking for people following user A in specific then uncomment this line
    (select count(t2.*)
     from table t2
     where t2.status = 1 and
           t2.user_two = t1.user_one and
           t2.user_one = t1.user_two) = 0

Using not exists

select
    t1.user_one,
    t1.user_two,
    t1.status
from
    table t1
where
    t1.status = 1 and
    -- t1.user_two = 'A' and -- If looking for people following user A in specific then uncomment this line
    not exists
        (select 1
         from table t2
         where t2.status = 1 and
               t2.user_two = t1.user_one and
               t2.user_one = t1.user_two)