Issues with understanding multiple joins

Question

I have the following tables:

users
 ____________
| id         |
| first_name |
|____________|

pre_task_plans
 ____________
| id         |
| creator_id | (fk user_id)
|____________|

pre_task_plan_users
 __________________
| id               |
| user_id          |
| pre_task_plan_id |
|__________________|

I am trying to get all users for pre_task_plans where I am the creator of (users.id 1)

I have

SELECT users.*
FROM users
JOIN pre_task_plans as ptp on ptp.creator_id = 1
JOIN pre_task_plan_users as ptpu on ptpu.pre_task_plan_id = ptp.id

But it is returning users that are not in pre_task_plan_users related to my pre_task_plans. What am I not understanding about how the 2nd JOIN is working?

PSQL 9.6.2

Answer 1

SELECT users.*, other.*
FROM users
JOIN pre_task_plans as ptp on ptp.creator_id = users.id
JOIN pre_task_plan_users as ptpu on ptpu.pre_task_plan_id = ptp.id
join users as other on pre_task_plan_users.user_id = other.id
where users.id = 1

You join each pre_task_plan to the user it created, and then you shrink the results down to you as user (so only those you created).

You should display some rows from pre_task_plans as well, like this you only see your own id and first_name