Início / coding / Perguntas / 79325001
Accepted
RobertW
Asked: 2025-01-03 05:25:15 +0800 CST

Obter todas as linhas relacionadas quando pelo menos uma corresponder

  • 772

Eu tenho uma photostabela, uma tagstabela e uma tabela de bridge chamada photos_tags. Imagine que há uma foto que foi marcada com dog, cat, e bird. Isso significa que haverá 1 linha em photos, 1 linha em tags, e 3 linhas em photos_tags.

Estou tentando escrever uma declaração SQL para buscar todas as 3 tags se uma delas corresponder. Cheguei até aqui:

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'

Mas isso só retorna as linhas onde tag = 'dog'.

Existe alguma maneira de realizar o que estou tentando fazer?

sql

4 respostas

  1. Best Answer

    Primeiro identifique todas as fotos que têm a etiqueta "dog", o que você já fez, mas vou reescrever para o CTE

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

    Agora use isso para filtrar sua consulta e mostrar tudo tagso photo_idque foi exibido:

    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)
    • 2

  2. @JNevill forneceu uma resposta que vou marcar como a solução correta. No interesse da completude, também vou compartilhar uma maneira alternativa que encontrei e que também parece funcionar:

    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))
    • 0

  3. Assumindo uma implementação padrão muitos-para-muitos , espero que isso seja o mais rápido e simples:

    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;

    Basta fazer o "Ring around the Rosie" uma vez.

    • 0

  4. Pode ser feito da seguinte forma:

    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'
);
    • -1

relate perguntas