CODEWITHSUNDEEP
sqlpostgresqlrelational-division
user2490003
user2490003

Reputation: 11920

Finding associations based on a logical AND instead of a logical OR

In my data model, a photo can have many tags associated with it.

So I have my photos table with two photos.

 id | file_name
----+----------------------
  1 | DSCN0317.jpg
  2 | DSCN0291.jpg

And I have my tags table with two tags

 id |      name
----+----------------
  5 | Vacation
  6 | Work

And I have a mapping table that ties two together. 

   photo_id | tag_id
----------+--------
        1 |      5
        2 |      5
        2 |      6

So in this example, photo 1 has a single tag (5) and photo 2 has multiple tags (5, 6)

In my app, I need to find all photos with a certain list of tags. More importantly, I need to do it in a single query if at all possible

(the reasoning for why it needs to be a single query is outside the scope of this question, and has to do with the specific implementation of my app).

For example, I want to "find all photos that have both tags 5 and 6"

SELECT DISTINCT "photos".*
FROM "photos"
INNER JOIN "photo_tags"
  ON "photo_tags"."photo_id" = "photos"."id"
WHERE "photo_tags"."tag_id" IN (5, 6)
;

But this query is incorrect, because it returns all photos that have tags 6 OR 7. I'm looking for photos that have 6 AND 7.

Is there any way I can transform the above query to do that?

Thanks!

Upvotes: 0

Views: 40

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

Aggregate the tags into arrays before joining. Then check if the required tags are contained by the array:

select distinct photos.*
from
    photos
    inner join (
        select photo_id as id, array_agg(tag_id) as tags
        from photo_tags
        group by photo_id
    ) photo_tags using(id)
where array[5, 6] <@ tags

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

Try this using aggregation and condition distinct count on the tag_id:

select p.*
from photos p
join (
    select 
        photo_id
    from tags
    where 
        tag_id in (5, 6)
    group by 
        photo_id
    having 
        count(distinct tag_id) = 2
) t on p.id = t.photo_id;

Or a correlated query:

select p.*
from photos p
where exists (
    select
        1
    from tags t
    where
        t.tag_id in (5, 6)
        and t.photo_id = p.id
    group by 
        id
    having 
        count(distinct tag_id) = 2
);

Upvotes: 1

krivtom
krivtom

Reputation: 24916

You can use EXISTS, to find photos, that have both ID 5 and ID 6:

SELECT "photos".*
FROM "photos"
WHERE EXISTS (
    SELECT "photo_tags"."tag_id" 
    FROM "photo_tags" 
    WHERE "photo_tags"."photo_id" = "photos"."id" 
        AND "photo_tags"."tag_id" = 5)
AND EXISTS (
    SELECT "photo_tags"."tag_id" 
    FROM "photo_tags" 
    WHERE "photo_tags"."photo_id" = "photos"."id" 
        AND "photo_tags"."tag_id" = 6)

Upvotes: 1

Related Questions