5
I have the following query:
SELECT funcionario.*, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa
FROM funcionario,emprego,empresa
WHERE empresa.id = emprego.id_empresa
AND funcionario.id = emprego.id_funcionario
AND emprego.data_admissao IS NOT NULL
AND empresa.id = 2 GROUP BY funcionario.id
She is presenting the following error:
#1055 - Expression #28 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'carteira.emprego.data_admissao' which is not functionally dependent on Columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Then I add the group by of the 'data_admissao' and the other fields for not error:
SELECT funcionario.*, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa
FROM funcionario,emprego,empresa
WHERE empresa.id = emprego.id_empresa
AND funcionario.id = emprego.id_funcionario
AND emprego.data_admissao IS NOT NULL
AND empresa.id = 2
GROUP BY funcionario.id, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa
So it works, however I wanted the items to be grouped by the 'functio.id' and when I run this query the records repeat themselves.
I would like the records to be grouped by the 'funcio.id' only, when running this second query it works, but the 'funcio.id' repeats itself.
Update
I removed "*" from the query just to make it easy to show the result here.
I executed:
SELECT funcionario.id, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa FROM funcionario,emprego,empresa WHERE empresa.id = emprego.id_empresa AND funcionario.id = emprego.id_funcionario AND emprego.data_admissao IS NOT NULL AND empresa.id = 2 GROUP BY funcionario.id, emprego.data_admissao, emprego.data_saida, emprego.data_dispensa
Upshot:
And if you add the records of each employee in an array?
– R.Santos
Using
INNER JOIN
also give that mistake?– Roberto de Campos
You’re looking for work. * and not working.ID, another thing you should look at is whether there are saved more than one date of admission/exit/discharge for the same employee.
– Caique Romero
I updated the question, look at the result.
– Wictor Chaves
@R.Santos I do not understand, you mean in the code receive everything and separate?
– Wictor Chaves
@Robertodecampos I haven’t tried with Ner Join.
– Wictor Chaves
Tries:
SELECT funcionario.id, max(emprego.data_admissao), max(emprego.data_saida), max(emprego.data_dispensa) 
FROM funcionario,emprego,empresa 
WHERE empresa.id = emprego.id_empresa
 AND funcionario.id = emprego.id_funcionario 
 AND emprego.data_admissao IS NOT NULL 
 AND empresa.id = 2 
GROUP BY funcionario.id
– Laércio Lopes
@Laérciolopes worked expensive, put and explains the query I will mark as the answer of the question (y)
– Wictor Chaves