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

Question

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       |
+----------+-----------+---------+

A hint: providing a [sqlfiddle](http://sqlfiddle.com/) with sample datacan increase the possibilty of getting a (correct) answer. — FirstOne, Jun 28 '16 at 12:27

score 2 · Answer 1 · answered Jun 28 '16 at 12:29

2

You can use NOT EXISTS for this:

SELECT USER_ONE, USER_TWO, STATUS 
FROM mytable AS t1
WHERE USER_TWO = 'A' AND STATUS = 1 AND 
      NOT EXISTS (SELECT 1
                  FROM mytable AS t2 
                  WHERE t2.USER_TWO = t1.USER_TWO AND
                        USER_ONE = 'A' AND STATUS = 1)

answered Jun 28 '16 at 12:29

Giorgos Betsos

71,379
9
63
98

Thanks for the answer, for some reason I got an empty response. I must have messed up the column names, I will revert back. – Parthapratim Neog Jun 28 '16 at 13:24

MonkeyZeus · Accepted Answer · 2016-06-28T13:20:11.860

2

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)

edited Jun 28 '16 at 13:20

answered Jun 28 '16 at 12:30

MonkeyZeus

20,375
4
36
77

1

@ParthapratimNeog Glad I could help! Please note that I have updated my answer with a `not exists` example because I have found it to [perform better than `count(*)`](http://stackoverflow.com/q/3271455/2191572) – MonkeyZeus Jun 28 '16 at 13:21
Totally makes sense. Will try it out with `not exists` and see. – Parthapratim Neog Jun 28 '16 at 13:26

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

2 Answers2