sql - Get all related rows when at least one matches - Stack Overflow

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 row in photos, 1 row in tags, and 3 rows in photos_tags.

I'm trying to write an SQL statement to fetch all 3 tags if one of them matches. I've gotten this far:

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 rows where tag = 'dog'.

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

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 row in photos, 1 row in tags, and 3 rows in photos_tags.

I'm trying to write an SQL statement to fetch all 3 tags if one of them matches. I've gotten this far:

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 rows where tag = 'dog'.

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

• sql
• postgresql
• join
• many-to-many

Share Improve this question Follow edited Jan 3 at 2:07 Erwin Brandstetter 662k158158 gold badges1.2k1.2k silver badges1.3k1.3k bronze badges asked Jan 2 at 21:25 RobertWRobertW 37211 gold badge66 silver badges1919 bronze badges 5

• Use a subquery or CTE to get the ids of interest. – Isolated Commented Jan 2 at 21:31
• Please provide a minimal reproducible example with sample data and desired results. – Dale K Commented Jan 2 at 21:32
• 1) Add to the question, as text, the table schema definitions. 2) Not sure why there would only be one record in tags or do you mean one record in tags for each record in photos_tags 3) At a guess ... t.tag IN ('dog', 'cat', 'bird'). – Adrian Klaver Commented Jan 2 at 21:32
• 1 Please add your answer as an answer not as part of your question – Dale K Commented Jan 2 at 22:07
• I've done it for you... please keep in mind for the future. – Dale K Commented Jan 2 at 22:28

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 2

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)

@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))

Assuming a standard many-to-many implementation, I expect this to be fastest and simplest:

SELECT photo_id, array_agg(t1.tag) AS tags
FROM   tags        t
JOIN   photos_tags pt  USING (tag_id)
JOIN   photos_tags pt1 USING (photo_id)
JOIN   tags        t1  ON t1.tag_id = pt1.tag_id
WHERE  t.tag = 'dog'
GROUP  BY 1;

Just do the "Ring around the Rosie" once.

It can be done as follows:

SELECT DISTINCT p.photo_id, t.tag
FROM photos p
JOIN photos_tags pt ON p.photo_id = pt.photo_id
JOIN tags t ON pt.tag_id = t.tag_id
WHERE p.photo_id IN (
    SELECT p1.photo_id
    FROM photos p1
    JOIN photos_tags pt1 ON p1.photo_id = pt1.photo_id
    JOIN tags t1 ON pt1.tag_id = t1.tag_id
    WHERE t1.tag = 'dog'
);