CODEWITHSUNDEEP
phpmysqlsql
user5899631
user5899631

Reputation:

MYSQL Query - Get the users that are not equal to the users I am following

I am trying to get the users I am not following and that isn't equal to me.

So far I have got this query: 

SELECT DISTINCT 
            u.id,
            u.username,
            u.profileImage,
            u.fullname,
            u.coverImage,
            u.bio,
            a.uuid,
            a.type
            FROM USERS u
            JOIN Activity a
            WHERE NOT u.id = 145
            AND a.id = 145
            AND type = 'follow'

145 is the current user.

I store following's in the Activity table, So I Don't want to get the users that are equal to the IdOtherUser in the row where id = 145.

When I follow someone it would be like this:

Id = 145(me)

IdOtherUser = 86(other person I follow, who I don't want to get from USERS table.)

type = 'follow'(type of action)

I am successfully getting all the users that aren't equal to me(145) but cannot seem to get the users that are equal to the people I follow!

Any ideas are much appreciated.

Thanks in advance

fiddle

I want to get the users that is not equal to 123(current user, and the people he is following user(145))

Upvotes: 1

Views: 327

Answers (1)

Shadow
Shadow

Reputation: 34231

There are various ways to accomplish the expected outcome. They are all based on using a subquery to determine if a user is followed by a given user. You can have this subquery as a derived table (in the from clause), or in a not in() or not exists() operator. I'll show you an example for the not exists() operator because it does not have to pull data from the users table, it merely checks if you have a record corresponding to the where criteria

select *
from users u1
where u1.id<>145  --not me
    and not exists (select 1
                    from activity a
                    where a.id=145 --users I follow
                        and a.IdOtherUser=u1.id
                        and a.type='follow')

Upvotes: 1

Related Questions