SQL query followers

Question

SQL query followers

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

Good!

I have two tables ( users and relationship ) and I want to show the followers (users that I follow, but they do not).

At the moment I only have to show me the users that I follow and they also show me.

Query (Users that follow):

select u.*
from relationship r
join relationship m
on m.idUser = r.idFollower and m.idFollower = r.idUser
join users u on u.id = r.idFollower
where r.idUser = 1 and r.status=1

Table Users:

- id
- name
- lastName

Relationship Table

- id
- idUser
- idFollower

Example: If I enter user 1, I would have to return user 5 and 8, because they do not follow me.

mysql sql

asked by vitho 19.12.2017 в 17:35

source

2 answers

duplicate events in a temple when doing mousemove on an element count records in a table

score 2 · Answer 1

You can use clause NOT EXISTS to not return users following the user 1 :

select u.*
  from relationship r
  join users u
    on u.id = r.idFollower
 where r.idUser = 1
   and r.status = 1
   and not exists (select null
                     from relationship r2
                    where r2.idUser = r.idFollower
                      and r2.idFollower = r.IdUser
                      and r2.status = 1)

You can also express the same idea using a LEFT JOIN if you prefer:

select u.*
  from relationship r
  join users u
    on u.id = r.idFollower
  left join relationship r2
    on r2.idUser = r.idFollower
   and r2.idFollower = r.IdUser
   and r2.status = 1
 where r.idUser = 1
   and r.status = 1
   and r2.id is null

score 0 · Answer 2

0

Try this

   select *
    from relationship r
    inner join Users m on m.id= r.idUser
    where r.idUser = 1 and r.status=1

answered by 19.12.2017 в 17:44