I have one-to-many relation between tables user and tag:
x
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
Advertisement
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.