CODEWITHSUNDEEP
mysqlsql
Kathiravan
Kathiravan

Reputation: 13

How to select users having only particular tag not another tag

I have the following db schema sql schema

I need to get the fname, lname of the user who have tag name 'A' and also they should not have tag name 'B'.For example User X has both tag A, tag B, He should not be included in the result whereas if another user Y only have Tag 'A' he should be included in the result. Is this possible without sub query?Do i have to alter the schema?

Upvotes: 1

Views: 51

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • You can GROUP BY on user id and name.
  • Use HAVING clause with SUM() aggregation to filter out cases.
  • If a user does not have a tag 'B', SUM(name = 'B') will be zero, post joins.

Try the following: 

SELECT 
  u.user_id, 
  u.fname, 
  u.lname 
FROM 
  user AS u 
JOIN tags AS t ON t.user_id = u.user_id 
JOIN tags_def AS td ON td.tag_id = t.tag_id 
GROUP BY 
  u.user_id, 
  u.fname, 
  u.lname 
HAVING 
  SUM(td.name = 'A') AND
  SUM(td.name = 'B') = 0

Upvotes: 1

Related Questions