Home Issues with understanding multiple joins
Reply: 1

Issues with understanding multiple joins

theartofbeing
1#
theartofbeing Published in 2018-01-12 18:59:46Z

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

Patrick Artner
2#
Patrick Artner Reply to 2018-01-12 19:16:08Z
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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.327752 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO