Postgres Join Table Error if First Select is Empty

Question

I am using Postgres. I have three tables: pictures, tags and picture_tags.

picture_tags is a join table.

Columns worth mentioning:

picture.id
tag.id
picture_tags.picture_id
picture_tags.tag_id

I am trying to run the following query to get all tags for picture id 100:

SELECT * FROM picture_tags
WHERE picture_id = 100
JOIN tags
ON tags.id = picture_tags.tag_id;

In my app there is a chance that a Picture may not have any Tags, thus there will be no picture_tags record to associate the two. When there is no Tags for a Picture, I get the following error:

syntax error at or near "JOIN"

since my select query returns nothing / is empty. If my select query is empty, is there a way to exit early before trying to join? Trying to make a fail-safe for when the select returns empty.

Sergey · Accepted Answer · 2022-12-08 17:35:44Z

1

SELECT P.*,PTG.*,TG.*
FROM PICTURE AS P
LEFT JOIN PICTURE_TAGS AS PTG ON P.PICTURE_ID=PTG.PICTURE_ID
LEFT JOIN TAGS AS TG ON PTG.TAG_ID=TG.TAG_ID
WHERE P.PICTURE_ID=100

answered Dec 8, 2022 at 17:35

Sergey

5,2801 gold badge9 silver badges12 bronze badges

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

2 Comments

Turing85 Over a year ago

Code-only answers are discouraged. We should rather explain the challenge, and how our solution tackles it.

Sergey Over a year ago

you are absolutely free to do this

Collectives™ on Stack Overflow

Postgres Join Table Error if First Select is Empty

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related