Postgres Exclusive Tag Search

I'm trying to return all rows which are associated with a user who is associated with ALL of the queried 'tags'. My table structure and desired output is below: admin.tags: user_id

Solution 1:

The query in the derived table gets you the user ids for users that have all specified tags and the outer query gets you the details.

select * 
from"system.users" s
join"admin.tags" a on s.id = a.user_id
join (select user_id 
    from"admin.tags"where tag in ('apple', 'pear')
    groupby user_id 
    having count(distinct tag) = 2
) t on s.id = t.user_id;

Note that this query would include users who have both tags that you search for but may have other too as long as they at least have the two specified.

With your sample data the output would be:

| id |         email | user_id |   tag |     detail |date| user_id ||----|---------------|---------|-------|------------|------------------------|---------||2| jane@test.com |2| grape |   blahhhhh | July, 28201500:00:00|2||2| jane@test.com |2| apple | blah, blah | July, 25201500:00:00|2||2| jane@test.com |2|  pear |   blahblah | July, 23201500:00:00|2||2| jane@test.com |2| apple |    blah... | July, 14201500:00:00|2|

If you want to exclude the row with grape just add a where tag in ('apple', 'pear') to the outer query too.

If you want only users that have only the searched for tags and none other (eg. exact division) you can change the query in the derived table to:

select user_id 
from "admin.tags" 
groupby user_id
havingsum(casewhen tag ='apple'then1else0end) >=1andsum(casewhen tag ='pear'then1else0end) >=1andsum(casewhen tag notin ('apple','pear') then1else0end) =0

This would not return anything given your sample data as user 2 also has grape

Sample SQL Fiddle

Solution 2:

Standard double-negation method for must-have-them-all kind of relational division problem: (I renamed date to zdate to avoid using a keyword as identifier)

---

-- For convenience: put search arguments into a temp table or CTE-- I cheat by extracting this from the admin_tags table-- (in fact, there should be a table with all possible tags somwhere) -- WITH needed_tags AS (-- SELECT DISTINCT tag-- FROM admin_tags-- WHERE tag IN ('apple' , 'pear' )-- )

---

-- Even better: directly use a VALUES() as a constructor-- (thanks to @jpw )WITH needed_tags(tag) AS (
    VALUES ('apple' ) , ( 'pear' )
    )
SELECT at.user_id , at.tag , at.detail , at.zdate
    , su.email
FROM admin_tags atJOIN system_users su ON su.id = at.user_id
WHERENOTEXISTS (
    SELECT*FROM needed_tags nt
    WHERENOTEXISTS (
        SELECT*FROM admin_tags nx
        WHERE nx.user_id = at.user_id
        AND nx.tag = nt.tag
        )
    )
    ;

Solution 3:

Use a correlated sub-select to count a user's number of different tags, and an un-correlated sub-select to count the number of different tags:

select at.user_id, at.tag, at.detail, at.date, su.email
from admin.tags atjoin system.users su on at.user_id = su.id
where (selectcount(distinct tag) from admin.tags at2
       where at2.user_id = at.user_id)
    = (selectcount(distinct tag) from admin.tag)