Postgres Query Using Join Table-CodePudding

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.

CodePudding user response：

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