PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Trying to get all related records when at least one matches

I have a photos table, a tags table, and a bridge table called photos_tags. Imagine there’s a photo which has been tagged with dog, cat, and bird. This means that there’ll be 1 record in photos, 1 record in tags, and 3 records in photos_tags.

What I’m trying to do is write a SQL statement that will fetch all 3 tags if even one of them matches. The basic SQL code I’ve written so far is this:

select distinct p.photo_id, t.tag from photos p
join photos_tags pt using (photo_id)
join tags t on pt.tag_id = t.tag_id and t.tag = 'dog'

But this only returns the records where tag = 'dog'.

Is there a way to accomplish what I’m trying to do?

Tags:

2

Answers


  1. 0

    First identify all of the photos that have the "dog" tag, which you already did but I’m going to rewrite for the CTE

    SELECT photo_id
FROM photos_tags
WHERE tag_id IN (SELECT tag_id FROM tags WHERE tag = 'dog')

    Now use this to filter your query to show all tags for any photo_id that was surfaced:

    WITH dog_photos AS 
(
   SELECT photo_id
   FROM photos_tags
   WHERE tag_id IN (SELECT tag_id FROM tags WHERE tag = 'dog')
)
SELECT p.photo_id, t.tag 
FROM photos p
  INNER JOIN photos_tags pt USING (photo_id)
  INNER JOIN tags t ON pt.tag_id = t.tag_id 
WHERE p.photo_id IN (SELECT photo_id FROM dog_photos)
    Login or Signup to reply.
  2. 0

    @JNevill provided an answer that I’m going to mark as the correct solution. In the interest of completeness, I’m also going to share an alternate way I found that also seems to work:

    select distinct p.photo_id, array_agg(t.tag) as tags from photos p
join photos_tags pt using (photo_id)
join tags t on pt.tag_id = t.tag_id
group by p.photo_id
having 'dog' = any(array_agg(t.tag))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search