How to Make Select Self Relationship N:N

Question

I am unable to perform an SQL query with relationship N:N. I have a table of users and another with self relationship user_has_usuario, who will be registered responsible and students (from a school) I would like to list the students by the CPF of the user(s) responsible type.

Model:

The query I’m using lists all students, but I’m not able to link to bring students through the CPF of responsible

SELECT DISTINCT
  u.idusuario,
  u.nome
from usuario u
INNER JOIN usuario_has_usuario uu ON u.idusuario = uu.aluno

Can someone help me?

SQL of the database:

-- -----------------------------------------------------
-- Table `testeboletim`.`tipo_user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testeboletim`.`tipo_user` (
  `idtipo_user` INT NOT NULL AUTO_INCREMENT,
  `funcao` VARCHAR(45) NULL,
  PRIMARY KEY (`idtipo_user`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testeboletim`.`usuario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testeboletim`.`usuario` (
  `idusuario` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NULL,
  `cpf` VARCHAR(20) NULL,
  `data_nasc` DATE NULL,
  `telefone` VARCHAR(20) NULL,
  `celular` VARCHAR(20) NULL,
  `email` VARCHAR(45) NULL,
  `tipo_user_idtipo_user` INT NOT NULL,
  PRIMARY KEY (`idusuario`, `tipo_user_idtipo_user`),
  INDEX `fk_usuario_tipo_user_idx` (`tipo_user_idtipo_user` ASC),
  CONSTRAINT `fk_usuario_tipo_user`
    FOREIGN KEY (`tipo_user_idtipo_user`)
    REFERENCES `testeboletim`.`tipo_user` (`idtipo_user`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testeboletim`.`usuario_has_usuario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testeboletim`.`usuario_has_usuario` (
  `responsavel` INT NOT NULL,
  `aluno` INT NOT NULL,
  PRIMARY KEY (`responsavel`, `aluno`),
  INDEX `fk_usuario_has_usuario_usuario2_idx` (`aluno` ASC),
  INDEX `fk_usuario_has_usuario_usuario1_idx` (`responsavel` ASC),
  CONSTRAINT `fk_usuario_has_usuario_usuario1`
    FOREIGN KEY (`responsavel`)
    REFERENCES `testeboletim`.`usuario` (`idusuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_usuario_has_usuario_usuario2`
    FOREIGN KEY (`aluno`)
    REFERENCES `testeboletim`.`usuario` (`idusuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Answer 1

SQL is like this:

SELECT DISTINCT a.*
FROM usuario a
INNER JOIN usuario_has_usuario h ON a.idusuario = h.aluno
INNER JOIN usuario r ON r.idusuario = h.responsavel
WHERE r.cpf = '12345678910';

For further information, see my other answer in the previous question.

Note that we start at the table usuario with the student, we navigate (using the INNER JOIN) for the relationship in usuario_has_usuario and then we sail again (again, with the INNER JOIN) to the table usuario, but this time using the other relationship.