Working with tags in MySQL

Try this:

select * from users where 
    id in ( 
      select user_tag.user_id from tags JOIN user_tag ON user_tag.tag_id = tags.id
      where tags.nome IN ('MG', 'SP') 
    )

    AND id IN (        
      select user_tag.user_id from tags JOIN user_tag ut ON user_tag.tag_id = tags.id
      where tags.nome IN ('basquete', 'futebol') 
    );

In this template you search for the tags you need and return the related users, for me it is simpler to understand, since the user must be related to the 2 groups of tags.

Firstly, I believe that the form you are working on is not functional because logically you do not use a reference such as the state of the user in a tag. For better treatment use a new column so it makes both queries and views easier.

Your solution is below (I tested it in SQLServer, so the language is like his) :

--construindo o contexto
CREATE DATABASE teste
GO
USE teste
GO
CREATE TABLE users    ( [id] INT, [nome] VARCHAR(100) )
GO
CREATE TABLE tags     ( [id] INT, [nome] VARCHAR(100) )
GO
CREATE TABLE user_tag ( [user_id] INT, [tag_id] INT )
GO
--populando users
INSERT INTO users VALUES (1,'Joao');
INSERT INTO users VALUES (2,'Pedro');
INSERT INTO users VALUES (3,'Maria');
INSERT INTO users VALUES (4,'Ana');
--populando tags
INSERT INTO tags VALUES (1,'SP');
INSERT INTO tags VALUES (2,'MG');
INSERT INTO tags VALUES (3,'Basquete');
INSERT INTO tags VALUES (4,'Futebol');
--populando user_tag
INSERT INTO user_tag(user_id,tag_id) VALUES (1,1); --> Joao  | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (2,1); --> Pedro | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (3,1); --> Maria | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (4,2); --> Ana   | MG
INSERT INTO user_tag(user_id,tag_id) VALUES (1,3); --> Joao  | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (1,4); --> Joao  | Futebol
INSERT INTO user_tag(user_id,tag_id) VALUES (2,1); --> Pedro | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (3,2); --> Maria | Futebol
INSERT INTO user_tag(user_id,tag_id) VALUES (4,3); --> Ana   | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (4,4); --> Ana   | Futebol
--solucao
SELECT 
    *
FROM 
    users
WHERE
    --verifica se é ou de SP ou de MG
    (   SELECT TOP 1 COUNT(*)
        FROM user_tag
        INNER JOIN tags ON (user_tag.tag_id = tags.id)
        WHERE 
            user_tag.user_id = users.id AND
            tags.nome IN ('SP','MG')
    )  = 1 AND
    --verifica se gosta de Basquete
    (   SELECT TOP 1 COUNT(*)
        FROM user_tag
        INNER JOIN tags ON (user_tag.tag_id = tags.id)
        WHERE
            user_tag.user_id = users.id AND
            tags.nome = 'Basquete'
    ) = 1 AND
    --verifica se gosta de Futebol
    (   SELECT TOP 1 COUNT(*) 
        FROM user_tag 
        INNER JOIN tags ON (user_tag.tag_id = tags.id) 
        WHERE
            user_tag.user_id = users.id AND
            tags.nome = 'Futebol'
    )  = 1

Basically what you should do is evaluate each query tag in a subquery in WHERE . By using TOP 1 in the subquery you ensure that the result will be a single record for the comparison in the original query. If you had more comparisons to make, you would just need to add one more subquery.

EDIT: Correcting so that there is no invalid response, new select follows, now it is very close to what has already been posted here, but it is still a select " strong> just another option to do the same thing, I left the select well bundled!.

Making use of the input information of Mr. LeandroLUK, but with a different solution in SELECT below:

SELECT distinct u.*,t1.nome,t2.nome FROM 
users u, tags t1, tags t2, user_tag r1, user_tag r2
WHERE
u.id = r1.user_id and
u.id = r2.user_id and
t1.id = r1.tag_id and 
t2.id = r2.tag_id and   
t1.nome in ('SP','MG') and (t2.nome in ('Futebol','Basquete'))

constructing the context

drop database if exists test;
CREATE DATABASE test;
USE test;

CREATE TABLE 'test'.'users' (
  'id' INT NOT NULL AUTO_INCREMENT  COMMENT '',
  'nome' VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY ('id')  COMMENT '');

CREATE TABLE 'test'.'tags' (
  'id' INT NOT NULL AUTO_INCREMENT  COMMENT '',
  'nome' VARCHAR(45) NULL COMMENT '',
  PRIMARY KEY ('id')  COMMENT '');

CREATE TABLE 'test'.'user_tag' (
  'id' INT NOT NULL AUTO_INCREMENT  COMMENT '',
  'user_id' INT NOT NULL COMMENT '',
  'tag_id' INT NOT NULL COMMENT '',
  PRIMARY KEY ('id')  COMMENT '');

-- REFERENCIANDO DAS FOREIN KEY
ALTER TABLE 'test'.'user_tag' 
ADD INDEX 'fk_tag_idx' ('tag_id' ASC)  COMMENT '',
ADD INDEX 'fk_user_idx' ('user_id' ASC)  COMMENT '';
ALTER TABLE 'test'.'user_tag' 
ADD CONSTRAINT 'fk_tag'
  FOREIGN KEY ('tag_id')
  REFERENCES 'test'.'tags' ('id')
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT 'fk_user'
  FOREIGN KEY ('user_id')
  REFERENCES 'test'.'users' ('id')
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;


-- populando users
INSERT INTO users VALUES (1,'Joao');
INSERT INTO users VALUES (2,'Pedro');
INSERT INTO users VALUES (3,'Maria');
INSERT INTO users VALUES (4,'Ana');
-- populando tags
INSERT INTO tags VALUES (1,'SP');
INSERT INTO tags VALUES (2,'MG');
INSERT INTO tags VALUES (3,'Basquete');
INSERT INTO tags VALUES (4,'Futebol');
-- populando user_tag
INSERT INTO user_tag(user_id,tag_id) VALUES (1,1); -- > Joao  | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (2,1); -- > Pedro | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (3,1); -- > Maria | SP
INSERT INTO user_tag(user_id,tag_id) VALUES (4,2); -- > Ana   | MG
INSERT INTO user_tag(user_id,tag_id) VALUES (1,3); -- > Joao  | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (1,4); -- > Joao  | Futebol
INSERT INTO user_tag(user_id,tag_id) VALUES (2,1); -- > Pedro | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (3,2); -- > Maria | Futebol
INSERT INTO user_tag(user_id,tag_id) VALUES (4,3); -- > Ana   | Basquete
INSERT INTO user_tag(user_id,tag_id) VALUES (4,4); -- > Ana   | Futebol

result

+----+------+------+----------+
| id | nome | nome | nome     |
+----+------+------+----------+
|  1 | Joao | SP   | Basquete |
|  1 | Joao | SP   | Futebol  |
|  4 | Ana  | MG   | Basquete |
|  4 | Ana  | MG   | Futebol  |
+----+------+------+----------+
4 rows in set (0.01 sec)