CODEWITHSUNDEEP
postgresql
Leeish
Leeish

Reputation: 5213

Join tables using IN clause where must match ALL values

I see some similar answers on here, but I'm having trouble in getting them to work. I essentially have two tables, one with content and one bridge to tags. I want to get content that has an associated tag. If I supply more than 1 tag, I only want content if it has all the tags, not just any of the tags

My first try was to do SELECT DISTINCT contentid FROM content INNER JOIN tags WHERE tagid IN (tagList). Obviously this results in content that has ANY passed in tag value, not ALL of the passed in tag values.

After reading I tried doing a NOT exists, but oddly it's not working how I think it should. My query is now:

SELECT DISTINCT contentid 
FROM content 
WHERE contentid NOT IN (
    SELECT contentid
    FROM content
    LEFT JOIN tags ON tags.contentid = content.contentid AND tags.tagid IN (tagList)
    WHERE tags.tagid IS NULL
)

EDIT (When testing the below, I remove the WHERE tags.tagid IS NULL to see the results, but even with it in I don't get the results as expected)

If I run the subquery and pass in a single value in the tagList that is missing from a specific contentid, I get a NULL value in the tagid. Therefore it would be remove as expected from the parent query. If I pass in a single value in the tagList that exists for a specific contentid, it's not in the subquery and thus exists in the parent query.

If I pass in more than one value (one existing and one missing for a specific contentid) I only get 1 row from the subquery and it's not the NULL one, so it doesn't filter it out as expected.

What am I missing here? And how can this be achieved?

Upvotes: 1

Views: 206

Answers (2)

Carlitos Way
Carlitos Way

Reputation: 3434

If I understand you correctly: "A Content has many Tags" ... So, given a list of N tags, you want the contents that have (at least) those N tags associated to them, right?

I think you can resolve this with the STRING_AGG() function like this ...

SELECT
    c.id,
    STRING_AGG(t.value, ', ' ORDER BY t.value) AS "TAG_LIST"
FROM
    content c
    INNER JOIN tag t ON c.id = t.content_id
WHERE
    t.value IN (<tagList>)
GROUP BY
    c.id
HAVING 
    STRING_AGG(t.value, ', ' ORDER BY t.value) = <tagList as CSV in ORDER!>
;

Example:

-- Return those tagged as first message:
SELECT
    c.id,
    STRING_AGG(t.value, ', ' ORDER BY t.value) AS "TAG_LIST"
FROM
    content c
    INNER JOIN tag t ON c.id = t.content_id
WHERE
    t.value IN ('message', 'first')
GROUP BY
    c.id
HAVING 
    STRING_AGG(t.value, ', ' ORDER BY t.value) = 'first, message'
;

I managed to prepare a small fiddle To test the concept (don't know how long those fiddle last)

Upvotes: 1

Blue Star
Blue Star

Reputation: 1952

Assuming that you're supplying this tag list externally (which appears to be the case), you can solve this by taking only those contentids with the correct number of matching tag rows:

SELECT content.contentid 
FROM content 
INNER JOIN tags ON tags.contentid = content.contentid AND tags.tagid IN (<tagList>)
GROUP BY content.contentid
HAVING COUNT(DISTINCT tagid) = <tag list length>

(If the tag list is a postgres array, the syntax will need to be changed slightly.)

Upvotes: 1

Related Questions