-2

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

GMB
  • 216,147
  • 25
  • 84
  • 135
fashuser
  • 2,152
  • 3
  • 29
  • 51

3 Answers3

1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Thank you very much for the prompt response. It looks like this sample perfectly match with my need. I'll double check it with different cases and will accept the answer! – fashuser Jun 04 '20 at 16:07
1

You can use conditional aggregation logic to get the tags:

select t.user_id
from tags t
where t.name in ('java', 'java script', 'C#')
group by t.user_id
having sum(case when t.name = 'C#' then 1 else 0 end) = 0;   -- no C#

Bringing in the user_name is just an additional join.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for prompt response! Could you please additionally advice how this query can be scaled, for instance if I would need to exclude more than few languages? – fashuser Jun 04 '20 at 16:10
  • 1
    @fashuser Ask a new (specific researched non-duplicate) question in a new post, not in comments. But that question is unresearched & asked many times before. [ask] – philipxy Jun 04 '20 at 16:49
1

Seems like a HAVING would work here:

SELECT U.id,
       U.username
FROM dbo.Users U
     JOIN dbo.Tags T
GROUP BY U.ID,
         U.username
HAVING COUNT(CASE T.[name] WHEN 'java' THEN 1 END) > 0
   AND COUNT(CASE T.[name] WHEN 'java script' THEN 1 END) > 0
   AND COUNT(CASE WHEN T.[name] NOT IN ('java','java script') THEN 1 END) = 0;

You could also replace the first 2 HAVING clauses with:

COUNT(DISTINCT CASE WHEN T.[name] IN ('java','java script') THEN T.[name] END) = 2
Thom A
  • 88,727
  • 11
  • 45
  • 75