Problem when performing join on tables with generalization

Question

Problem when performing join on tables with generalization

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I'm having trouble performing a JOIN on tables so the problem is this:

In a bulletin board system, I have users, including students, school officials, and school employees. I need to generate an SQL statement that, when informing the CPF of the person (s), will list all the students related to the CPF of those responsible (s).

Follow the template:

Isthisthebestwaytoimplementthis"Generalization" and this relationship between those responsible and the students, since they are all users? Can anyone help me?

SQL code:

-- -----------------------------------------------------
-- Table 'testeboletim'.'tipo_usuario'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'testeboletim'.'tipo_usuario' (
  'idtipo_usuario' INT NOT NULL AUTO_INCREMENT,
  'funcao' VARCHAR(45) NULL,
  PRIMARY KEY ('idtipo_usuario'))
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_usuario_idtipo_usuario' INT NOT NULL,
  PRIMARY KEY ('idusuario', 'tipo_usuario_idtipo_usuario'),
  INDEX 'fk_usuario_tipo_usuario_idx' ('tipo_usuario_idtipo_usuario' ASC),
  CONSTRAINT 'fk_usuario_tipo_usuario'
    FOREIGN KEY ('tipo_usuario_idtipo_usuario')
    REFERENCES 'testeboletim'.'tipo_usuario' ('idtipo_usuario')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'testeboletim'.'aluno'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'testeboletim'.'aluno' (
  'idaluno' INT NOT NULL AUTO_INCREMENT,
  'usuario_idusuario' INT NOT NULL,
  'usuario_tipo_usuario_idtipo_usuario' INT NOT NULL,
  PRIMARY KEY ('idaluno', 'usuario_idusuario', 'usuario_tipo_usuario_idtipo_usuario'),
  INDEX 'fk_aluno_usuario1_idx' ('usuario_idusuario' ASC, 'usuario_tipo_usuario_idtipo_usuario' ASC),
  CONSTRAINT 'fk_aluno_usuario1'
    FOREIGN KEY ('usuario_idusuario' , 'usuario_tipo_usuario_idtipo_usuario')
    REFERENCES 'testeboletim'.'usuario' ('idusuario' , 'tipo_usuario_idtipo_usuario')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'testeboletim'.'responsavel'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'testeboletim'.'responsavel' (
  'idresponsavel' INT NOT NULL AUTO_INCREMENT,
  'usuario_idusuario' INT NOT NULL,
  'usuario_tipo_usuario_idtipo_usuario' INT NOT NULL,
  PRIMARY KEY ('idresponsavel', 'usuario_idusuario', 'usuario_tipo_usuario_idtipo_usuario'),
  INDEX 'fk_responsavel_usuario1_idx' ('usuario_idusuario' ASC, 'usuario_tipo_usuario_idtipo_usuario' ASC),
  CONSTRAINT 'fk_responsavel_usuario1'
    FOREIGN KEY ('usuario_idusuario' , 'usuario_tipo_usuario_idtipo_usuario')
    REFERENCES 'testeboletim'.'usuario' ('idusuario' , 'tipo_usuario_idtipo_usuario')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'testeboletim'.'aluno_has_responsavel'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'testeboletim'.'aluno_has_responsavel' (
  'aluno_idaluno' INT NOT NULL,
  'aluno_usuario_idusuario' INT NOT NULL,
  'responsavel_idresponsavel' INT NOT NULL,
  'responsavel_usuario_idusuario' INT NOT NULL,
  PRIMARY KEY ('aluno_idaluno', 'aluno_usuario_idusuario', 'responsavel_idresponsavel', 'responsavel_usuario_idusuario'),
  INDEX 'fk_aluno_has_responsavel_responsavel1_idx' ('responsavel_idresponsavel' ASC, 'responsavel_usuario_idusuario' ASC),
  INDEX 'fk_aluno_has_responsavel_aluno1_idx' ('aluno_idaluno' ASC, 'aluno_usuario_idusuario' ASC),
  CONSTRAINT 'fk_aluno_has_responsavel_aluno1'
    FOREIGN KEY ('aluno_idaluno' , 'aluno_usuario_idusuario')
    REFERENCES 'testeboletim'.'aluno' ('idaluno' , 'usuario_idusuario')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_aluno_has_responsavel_responsavel1'
    FOREIGN KEY ('responsavel_idresponsavel' , 'responsavel_usuario_idusuario')
    REFERENCES 'testeboletim'.'responsavel' ('idresponsavel' , 'usuario_idusuario')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

mysql sql join

asked by anonymous 23.12.2016 / 03:33

2 answers

1

In fact what you need requires a smaller number of fields, tables and therefore relationships:

The search select would look something like:

SELECT
    A.*
FROM Responsavel R
INNER JOIN Usuario U ON R.idUsuario = U.idUsuario
INNER JOIN Aluno A ON A.idResponsavel = R.idResponsavel
WHERE
    R.cpf = '99.999.999-99'

23.12.2016 / 12:47

remote request with php jQuery-autoComplete Fill class with bank result

score 0 · Accepted Answer

A generalization relationship can be modeled in a number of ways. The way I think it will serve you better is this:

The most generic table has some primary key.
The most specific tables have the same primary key as the most generic table.
The primary key of more specific tables are also foreign keys of the more generic table.

In addition, if your table has a AUTO_INCREMENT field then this field is sufficient to be the primary key. That is, in this case you do not need to have any more fields in the primary key besides what is marked as AUTO_INCREMENT . This will also simplify the foreign keys that are exported from that table and the indexes used.

Never use ON DELETE NO ACTION or ON UPDATE NO ACTION . This will break your referential integrity . Even more that you're using InnoDB that was designed with the idea of respecting referential integrity (something MyIsam does not do).

Here's what your SQL code would look like, thinking of what I've explained above:

CREATE TABLE IF NOT EXISTS 'testeboletim'.'tipo_usuario' (
  'id_tipo_usuario' INT NOT NULL AUTO_INCREMENT,
  'funcao' VARCHAR(45) NULL,
  PRIMARY KEY ('id_tipo_usuario')
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS 'testeboletim'.'usuario' (
  'id_usuario' 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,
  'id_tipo_usuario' INT NOT NULL,
  PRIMARY KEY ('idusuario'),
  INDEX 'fk_usuario_tipo_usuario_idx' ('tipo_usuario_idtipo_usuario' ASC),
  CONSTRAINT 'fk_usuario_tipo_usuario'
    FOREIGN KEY ('id_tipo_usuario')
    REFERENCES 'testeboletim'.'tipo_usuario' ('id_tipo_usuario')
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS 'testeboletim'.'aluno' (
  'id_aluno' INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY ('id_aluno'),
  CONSTRAINT 'fk_aluno_usuario'
    FOREIGN KEY ('id_aluno')
    REFERENCES 'testeboletim'.'usuario' ('id_usuario')
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS 'testeboletim'.'responsavel' (
  'id_responsavel' INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY ('id_responsavel'),
  CONSTRAINT 'fk_responsavel_usuario'
    FOREIGN KEY ('id_responsavel')
    REFERENCES 'testeboletim'.'usuario' ('id_usuario')
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS 'testeboletim'.'aluno_has_responsavel' (
  'id_aluno' INT NOT NULL,
  'id_responsavel' INT NOT NULL,
  PRIMARY KEY ('id_aluno', 'id_responsavel'),
  INDEX 'fk_aluno_has_responsavel_responsavel_idx' ('id_responsavel' ASC),
  INDEX 'fk_aluno_has_responsavel_aluno_idx' ('id_aluno' ASC),
  CONSTRAINT 'fk_aluno_has_responsavel_aluno'
    FOREIGN KEY ('id_aluno')
    REFERENCES 'testeboletim'.'aluno' ('id_aluno'),
  CONSTRAINT 'fk_aluno_has_responsavel_responsavel'
    FOREIGN KEY ('id_responsavel')
    REFERENCES 'testeboletim'.'responsavel' ('id_responsavel')
) ENGINE = InnoDB;

Note that the foreign keys exported are much simpler and the indexes will also be much simpler. Indexes that match the primary key are unnecessary, since the database will already implicitly create them.

In addition, you do not really need that which is below, even though it was automatically generated. So I removed:

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Finally, your SQL query looks like this:

SELECT DISTINCT ua.*
FROM usuario ua
INNER JOIN aluno a ON ua.id_usuario = a.id_aluno
INNER JOIN aluno_has_responsavel h ON a.id_aluno = h.id_aluno
INNER JOIN responsavel r ON h.id_responsavel = r.id_responsavel
INNER JOIN usuario ur ON ur.id_usuario = r.id_responsavel
WHERE ur.cpf = '12345678910';

Oh, and I do not know if you really need the tipo_usuario table. If the idea is to know if a user is a student, collaborator or responsible, then it is unnecessary. To do this, just look at which of the tables it appears.