CODEWITHSUNDEEP
sqlpostgresql
user2465134
user2465134

Reputation: 9813

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.

Upvotes: 1

Views: 28

Answers (1)

nbk
nbk

Reputation: 49410

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

Upvotes: 1

Related Questions