How to query rows depending on multiple to-many properties?

I have one-to-many relation between tables user and tag:

Users:
id    username
--------------
 1    Bob     
 2    Alice   
 3    Eve   

Tags:
id   user_id   name
--------------------
 1   1         java         // Bobs tags...
 2   1         java script
 3   1         C#   
 4   2         java         // Alices tags...
 5   3         java         // Eves tags...
 6   3         java script

My goal is to extract all users with tags java or java script only, but not users which have java, java script and C# together.

As output of the query I expect to receive following result:

Result:
id   username
--------------
2    Alice     
3    Eve

I’ve tried to use query from SQL one-to-many relationship – How to SELECT rows depending on multiple to-many properties?, but as I noticed it is a bit different idea behind of it

Answer

One efficient, although lenghty to type, option uses exists:

select u.*
from users u
where 
    not exists(select 1 from tags t where t.user_id = u.id and t.name = 'C#')
    and exists(select 1 from tags t where t.user_id = u.id and t.name = 'java ')
    and exists(select 1 from tags t where t.user_id = u.id and t.name = 'java script')

With an index on tags(user_id, name), this should be very fast.

Advertisement

Answer