Obtaining the number of dependents of an official

Question

I’m trying to do a search between 3 tables: Employee,Dependents,Function_has_dependents; I want to do the research that shows the number of dependents of each employee. I did the following:

    select dependente.*,funcionarios.*  
      from funcionarios f,dependente d,dependente_has_funcionarios df 
      where f.Matricula = df.Matricula and  
      df.Dependente_idDependente = d.idDependente;

But it didn’t work. PS:Employee PK registration.

Answer 1

Hello, I played your scenario here! and I set up what you might be wanting:

CREATE TABLE Funcionario
(
    Id                    INT NOT NULL AUTO_INCREMENT,
    Nome                  VARCHAR(100) NOT NULL,   
    PRIMARY KEY (id)
);

CREATE TABLE Dependente
(
    Id                    INT NOT NULL AUTO_INCREMENT,
    FuncionarioId         INT NOT NULL,
    Nome                  VARCHAR(100) NOT NULL,   
    PRIMARY KEY (Id),
    FOREIGN KEY (Id) REFERENCES Funcionario(id)
);

CREATE TABLE Dependente_Has_Funcionario 
(
    Id                    INT NOT NULL AUTO_INCREMENT,
    FuncionarioId         INT NOT NULL,
    DependenteId          INT NOT NULL,
    PRIMARY KEY (Id)
);

insert into Funcionario (Id,Nome) values ('101','Rodrigo Mendez');
insert into Funcionario (Id,Nome) values ('102','Marco Ciciliano');
insert into Funcionario (Id,Nome) values ('103','Paulo Guerra');
insert into Funcionario (Id,Nome) values ('104','Renato Teixeira');
insert into Funcionario (Id,Nome) values ('105','Antonio Marcos');

insert into Dependente (Id,FuncionarioId,Nome) values ('101','101','Mauricio Filho');
insert into Dependente (Id,FuncionarioId,Nome) values ('102','101','Arnaldo Filho');
insert into Dependente (Id,FuncionarioId,Nome) values ('103','102','Jenival Filho');
insert into Dependente (Id,FuncionarioId,Nome) values ('104','102','Walter Filho');
insert into Dependente (Id,FuncionarioId,Nome) values ('105','105','Nataly Filho');

insert into Dependente_Has_Funcionario (Id,FuncionarioId,DependenteId) values ('101','101','101');
insert into Dependente_Has_Funcionario (Id,FuncionarioId,DependenteId) values ('102','101','102');
insert into Dependente_Has_Funcionario (Id,FuncionarioId,DependenteId) values ('103','102','103');

You can do using 2 tables with the "GROUP BY" clause with a "COUNT" between Employee and Dependent:
_{See on Sqlfiddle}

SELECT
    a.Nome,
    COUNT(*) Dependentes
FROM 
    Funcionario a,
    Dependente b
WHERE 
    a.id = b.FuncionarioId     
GROUP BY 
    a.Nome;

or by what I noticed using the third table that is Funcio related to the table 'dependente_has_funcionario'
_{See on Sqlfiddle}

SELECT
    a.Nome,
    COUNT(*) Dependentes
FROM 
    Funcionario a,
    Dependente_Has_Funcionario b    
WHERE 
    a.id = b.FuncionarioId   
GROUP BY 
    a.Nome;

I hope I helped! Oks!

Answer 2

You did not specify what does not work, but I suggest changing your query to use the clause JOIN (LEFT JOIN in that case because an official may not have dependents).

SELECT d.*, f.*
FROM funcionarios f
LEFT JOIN dependente_has_funcionarios df ON df.Matricula = f.Matricula
LEFT JOIN dependente d on df.Dependente_idDependente = d.idDependente

I also suggest if you only want to know the number of dependents, specify the columns you want to use and use COUNT.

SELECT f.Matricula, COUNT(df.*) As QtdDependetes
FROM funcionarios f
LEFT JOIN dependente_has_funcionarios df ON df.Matricula = f.Matricula
LEFT JOIN dependente d on df.Dependente_idDependente = d.idDependente
GROUP BY f.Matricula