Postgres Query Using Join Table

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.

Answer

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';