(SELECT JOIN?) How to get a quantity of records from a specific column, from another table (when there is a foreign key)

0

My list screen shows the data in the "PERSON" table.

There is a table called REGISTRATION that has the column idpessoa referencing. In this registration there is a field called EVADIDO to know if the person escaped from that course that enrolled.

How do I know, in my LISTING, how many times this person (student) has already evaded an enrollment (course), and does not have this field in the person table, only by the enrollment table. >

I think it's easy to understand, my system currently uses this select:

SELECT pe.* FROM pessoas pe
INNER JOIN cidades ci ON (ci.idcidade=pe.idcidade) 
INNER JOIN estados es ON (ci.idestado=es.idestado) 
WHERE pe.removido = 'N';

This Inner City and State JOIN is also essential because in my listing the name of the city appears by reference by an ID (the cities and states are registered in the bank) ... the same for state .. I need to leave all this in of the same select.

--
-- Estrutura da tabela 'pessoas'
--

CREATE TABLE 'pessoas' (
  'idpessoa' int(10) UNSIGNED NOT NULL,
  'idcidade' int(10) UNSIGNED NOT NULL,
  'ativo' enum('S','N') NOT NULL DEFAULT 'S',
  'removido' enum('S','N') NOT NULL DEFAULT 'N',
  'login_ativo' enum('S','N') NOT NULL DEFAULT 'S',
  'data_cad' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  'nome' varchar(100) NOT NULL,
  'estado_civil' enum('S','C','D','V') DEFAULT NULL,
  'etnia' enum('B','P','N','O','A') NOT NULL,
  'data_nasc' date NOT NULL,
  'naturalidade' varchar(100) DEFAULT NULL,
  'documento' varchar(20) NOT NULL,
  'rg' varchar(20) DEFAULT NULL,
  'rg_orgao_emissor' varchar(20) DEFAULT NULL,
  'rg_data_emissao' date DEFAULT NULL,
  'nis' varchar(11) NOT NULL,
  'filiacao_mae' varchar(100) DEFAULT NULL,
  'filiacao_pai' varchar(100) DEFAULT NULL,
  'cep' int(8) UNSIGNED DEFAULT NULL,
  'endereco' varchar(100) DEFAULT NULL,
  'bairro' varchar(100) DEFAULT NULL,
  'numero' varchar(10) DEFAULT NULL,
  'complemento' varchar(100) DEFAULT NULL,
  'login' varchar(100) DEFAULT NULL,
  'email' varchar(100) DEFAULT NULL,
  'sit_ocupacional' enum('EM','DE','AU','AP') DEFAULT NULL,
  'emp_profissao' varchar(30) DEFAULT NULL,
  'emp_empresa' varchar(30) DEFAULT NULL,
  'pcd_visual' enum('BAI','CEG') DEFAULT NULL,
  'pcd_mental' enum('MEN') DEFAULT NULL,
  'pcd_auditiva' enum('SBP','SBT') DEFAULT NULL,
  'pcd_fisica' enum('AMP','1MS','1MI','CAD','NAN','AMS','AMI','OST','PAC','PAR') DEFAULT NULL,
  'vinc_empregaticio' enum('CA','AU','ME','FP','OU','NT') DEFAULT NULL,
  'pcd' enum('S','N') NOT NULL,
  'pcd_qual' enum('F','A','M','V') DEFAULT NULL,
  'pcd_grau' enum('L','M','A') DEFAULT NULL,
  'nacionalidade' varchar(100) NOT NULL,
  'escolaridade' enum('FI','FC','MI','MC','SI','SC') NOT NULL,
  'formacao' varchar(30) DEFAULT NULL,
  'sit_escolaridade' enum('C','I','T') DEFAULT NULL,
  'sit_escolaridade_curso' varchar(30) DEFAULT NULL,
  'sit_escolaridade_periodo' enum('1','2','3','4','5','6','7','8','9','10') DEFAULT NULL,
  'instituicao' varchar(100) DEFAULT NULL,
  'curso_fundat' enum('S','N') DEFAULT NULL,
  'curso_fundat_qual' varchar(200) DEFAULT NULL,
  'nr_membros_familia' int(11) NOT NULL,
  'renda_familiar' enum('1','2','3','4','5','6') NOT NULL,
  'password' varchar(128) DEFAULT NULL,
  'observacoes' text,
  'prog_federal' enum('S','N') DEFAULT NULL,
  'prog_federal_qual' varchar(200) DEFAULT NULL,
  'encaminhado_instituicao' enum('S','N') NOT NULL,
  'encaminhado_instituicao_qual' varchar(200) DEFAULT NULL,
  'telefone1' varchar(15) DEFAULT NULL,
  'telefone2' varchar(15) DEFAULT NULL,
  'ultimo_view' datetime DEFAULT NULL,
  'ultima_senha' datetime NOT NULL,
  'avatar_nome' varchar(100) DEFAULT NULL,
  'avatar_servidor' varchar(100) DEFAULT NULL,
  'avatar_tipo' varchar(100) DEFAULT NULL,
  'avatar_tamanho' int(10) UNSIGNED DEFAULT NULL,
  'sexo' enum('M','F','N') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Estrutura da tabela 'matriculas'
--

CREATE TABLE 'matriculas' (
  'idmatricula' int(10) UNSIGNED NOT NULL,
  'idhorariooferta' int(10) UNSIGNED NOT NULL,
  'idpessoa' int(10) UNSIGNED NOT NULL,
  'removido' enum('S','N') NOT NULL DEFAULT 'N',
  'data_cad' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  'matricula' int(8) UNSIGNED ZEROFILL DEFAULT NULL,
  'situacao' enum('M','I') NOT NULL DEFAULT 'M',
  'evadido' enum('S','N') NOT NULL DEFAULT 'N',
  'desistente' enum('S','N') DEFAULT 'N',
  'reprovado' enum('S','N') DEFAULT 'N'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
asked by anonymous 25.02.2018 / 22:50

1 answer

0

You can use a subselect with select count, for example:

SELECT pe.*, (SELECT count(0) 
              FROM matriculas ma
              WHERE ma.idpessoa = pe.idpessoa 
                  AND ma.evadido = 'S') quantidade_evasao 
FROM pessoas pe
INNER JOIN cidades ci ON (ci.idcidade=pe.idcidade) 
INNER JOIN estados es ON (ci.idestado=es.idestado) 
WHERE pe.removido = 'N';

But in this case it shows amount of course independent evocation, but it would only add more clauses in the subselect according to what you need.

    
26.02.2018 / 15:42