How to do select auto relationship N: N

Question

How to do select auto relationship N: N

Navigation

#1 by (3 votes)

3

I am not able to perform a SQL query with N: N relationship. I have a user table and one with user_has_usuario self-relationship, which will be registered responsible and students (from a school) would like to list the students by the CPF of the type responsible user.

Template:

The query I am using lists all students, but I am not able to bind to bring the students by the CPF of those responsible.

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

Can anyone help me?

Bank SQL:

-- -----------------------------------------------------
-- 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;

mysql sql select

asked by anonymous 23.12.2016 / 20:22

1 answer

Change sequence value with subselect Is it possible to only use get without the set in Object.defineProperty?

score 3 · Accepted Answer

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 more information, see the my other answer in the previous question .

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