Postgres Query Using Join Table

Question

I have 3 tables:

users, public_pictures, pictures

Import properties:

users.id

public_pictures.user_id

public_pictures.picture_id

pictures.id

public_pictures is a join table between users and pictures with only those two properties above. Both are foreign keys to their respective table.

Given a user.id, I want to get all their public pictures. I tried something like:

SELECT * FROM pictures
INNER JOIN public_pictures
ON pictures.user_id = '1';

but this just returns all the user's images instead of only the public ones.

nbk · Accepted Answer · 2022-07-17 22:31:30Z

1

You need to join the tables with their joining columns and as far as i can see only public_pictures has a user_id.

so the query must look like

SELECT * FROM pictures pi
INNER JOIN public_pictures pp
ON pp.picture_id = pi.id
WHERE pp.user_id = '1';

answered Jul 17, 2022 at 22:31

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Query Using Join Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related