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

Question

My listing screen shows the table data "PERSON".

There is a table called MATRICULA q 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 LIST, how many times this person (student) has already evaded a enrollment (course), and there is this field in the person table, only by the table of enrollments.

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 JOIN Cities and State is essential also because in my listing appears the name of the city by reference by an ID (cities and states are registered in the bank)... the same for the state.. I need to keep it all within 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;

Answer 1

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

However in this case it shows independent evation amount of the course, but would just add more clauses in the subselect according to what you need.