Reputation: 5213
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: 205
Reputation: 3424
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
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