Get the number of dependents of an employee

Question

Get the number of dependents of an employee

Navigation

#1 by (6 votes)
#2 by (2 votes)

3

I'm trying to do a search between 3 tables: Employee, Dependents, Employee_Dependent; I want to do the research where I show 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 did not work. PS: Enrollment is official PK.

mysql select

asked by anonymous 02.05.2015 / 20:54

2 answers

Doubts about using the Java 8 stream Working @classmethod

score 6 · Answer 1

Hello, I reproduce your scenery here! and put what you might want:

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 this by using 2 tables with the clause "GROUP BY" with a "COUNT" between Employee and Dependent: See 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 Official related to the 'Dependent_Has_Funtionary' table
See 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 have helped! Oks!

score 2 · Answer 2

You did not specify what did not work, but I suggest changing your query to use the JOIN ( LEFT JOIN clause in this case as an employee might 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 want to know only 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