Search code examples
pythonmysqljoinmany-to-many

How to select from multiple tables based on many to many relationship?

I am trying to create a select statement that will return a list of users following a specific user , based on conditions from two tables. I have the following relationship created in my database. (incoming 10/10 ms paint skills)

Table Design

What I want to output for example , are the users following tim , (follower) , which have notifications set as 0 in the users table. From there I can do what I want programmatically with the results.

So far I have tried inner join, 

SELECT users.username FROM users
INNER JOIN followers
ON users.username = followers.followed
WHERE followers.followed = users.username AND 
users.username = 'tim'

and I can't seem to figure out how to format it properly.

Solution

  • Would be much more cleaner if you use instead of the 'followed' string, the 'userid' and instead of the 'follower' the 'followerid'.

    In this case would be userid=1 (tim user id) in followers table.

    Then the query would be:

    select * from users where notifications=0 and id in (select followerid from 
followers where userid=1)

    If you need to stick to that structure:

    select * from users where notifications=0 and username like (select follower from 
followers where followed like 'tim')