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

i stuck problem. consider following table. know value a(i.e. can use select * table user_one = a). tried doing self join, didn't help.

given table

+----------+-----------+---------+ | user_one |  user_two |  status | +----------+-----------+---------+ |          |           |         | |        | b         | 0       | |          |           |         | | b        |         | 1       | |          |           |         | |        | c         | 1       | |          |           |         | | c        |         | 1       | |          |           |         | | d        |         | 1       | |          |           |         | |        | e         | 0       | +----------+-----------+---------+ 

my desired result needs following. imagine user_one following user_two if status 1.status 0 means, user_one following user_two, unfollowed user_two. need users following "a". notice don't want, rows both following each other (a -> b) , (b -> a) both has status 1. question following response like, "find me people following a, not following them", makes sense? little appreciated.

desired rows

+----------+-----------+---------+ | user_one |  user_two |  status | +----------+-----------+---------+ |          |           |         | | b        |         | 1       | |          |           |         | | d        |         | 1       | +----------+-----------+---------+ 

this should work:

using count(*)

select     t1.user_one,     t1.user_two,     t1.status     table t1     t1.status = 1 ,     -- t1.user_two = 'a' , -- if looking people following user in specific uncomment line     (select count(t2.*)      table t2      t2.status = 1 ,            t2.user_two = t1.user_one ,            t2.user_one = t1.user_two) = 0 

using not exists

select     t1.user_one,     t1.user_two,     t1.status     table t1     t1.status = 1 ,     -- t1.user_two = 'a' , -- if looking people following user in specific uncomment line     not exists         (select 1          table t2          t2.status = 1 ,                t2.user_two = t1.user_one ,                t2.user_one = t1.user_two)