2
How exactly do I get this column valueAuxilio does not have its values duplicated, that is, instead of four result lines would be only two.
SELECT p.nome,
d.nome,
c.valorAuxilio
FROM sca_pessoa p
INNER JOIN sca_dependente d ON p.idPessoa = d.idPessoa
INNER JOIN sispro_contrato c ON p.idPessoa = c.idPessoa
WHERE p.nome LIKE '%ADEMAR MAGA%'
AND c.idSituacaoContrato = 3 ORDER BY c.valorAuxilio;
3
You’d have to group over the field valorAuxilio and use aggregation functions on top of the other columns.
SELECT GROUP_CONCAT(p.nome SEPARATOR ';'),
GROUP_CONCAT(d.nome SEPARATOR ';'),
c.valorAuxilio
FROM sca_pessoa p
INNER JOIN sca_dependente d ON p.idPessoa = d.idPessoa
INNER JOIN sispro_contrato c ON p.idPessoa = c.idPessoa
WHERE p.nome LIKE '%ADEMAR MAGA%'
AND c.idSituacaoContrato = 3 ORDER BY c.valorAuxilio;
GROUP BY c.valorAuxilio;
Reference:
I don’t understand your code.
I’m grouping the results using the column valorAuxilio as pivot, so that there are no duplicates in the result of this column... but for this, I have to add the other columns, because each of them has two values for each value of valorAuxilio... I chose the aggregation function GROUP_CONCAT for this, to concatenate the results of these columns, as they are strings.
What’s tying me up is, why is there a need to group? If they were LEFT JOINI would understand, but INNER indicate duplicate data in some table. EDIT I think now I understand, I will comment above.
This discussion is even longer here: http://answall.com/questions/12273/valor-de-column_duplicatehow to avoid
I took a negative without explanation!!! = D
@Miguelangelo here at Sopt just don’t understand, that already negative :D
0
Guy tries to use LEFT JOIN:
SELECT p.nome,
d.nome,
c.valorAuxilio
FROM sca_pessoa p
LEFT JOIN sca_dependente d ON p.idPessoa = d.idPessoa
LEFT JOIN sispro_contrato c ON p.idPessoa = c.idPessoa
WHERE p.nome LIKE '%ADEMAR MAGA%'
AND c.idSituacaoContrato = 3 ORDER BY c.valorAuxilio;
Put the tables Scription there for us to take a look.
Browser other questions tagged mysql sql
You are not signed in. Login or sign up in order to post.
Tried a
group by?– rray
You could have placed the code without being an image... it makes it enormously difficult for the person who wants to give an answer. =\
– Miguel Angelo
Sorry Miguel, it’s because I wanted to put the result together, thinking it would improve visualization next time I correct myself on this.
– phpricardo
The visualization is important, as is the code. Now it’s perfect! = D
– Miguel Angelo
The data is duplicated in one of the tables. Why?
– bfavaretto
@bfavaretto ai vc got me. kkk I don’t know if I can tell if they are duplicate values it is as if they were different contracts, but values that is equal. #Trainee
– phpricardo
What I understand is that Ademar has two contracts with two dependents in each. To bring one line per contract, one must join the names of these dependents in a single line, as in Miguel Angelo’s reply. I mean, it’s not a duplication like I originally thought.
– bfavaretto
@bfavaretto understood what you say, but due to lack of knowledge I think I do not know how to explain. But this _contract table is like a "link" to other tables and this valueAuxilio also exists in the table with the same value called reportIndividualDependent.
– phpricardo
Test Miguel Angelo’s query and you’ll understand how it works. It groups data from the same contract into a single line. As each contract has 2 dependents, they will appear in a single column, separated by
;as specified inGROUP_CONCAT.– bfavaretto
The order of
INNER JOINI feel a strange feeling.– Miguel Angelo
By the way, read the comments of the other issue, and see the edits. I think it pays to stay there. This has been happening a lot here of mutant duplicates, then I think it’s a case to discuss at the goal.
– Bacco
My head used to object orientation would expect this order: sca_person -> sispro_contract -> sca_dependent
– Miguel Angelo
@phpricardo if we continue the discussion in the original, people will already have access to everything that has already been tried, and it will help everyone understand the problem. Splitting the issue only gets in everyone’s way. Remember that you can at any time add in that more details, increasing the question with the details you need.
– Bacco
It’s okay with me, I just thought the ideas were different, so open a new.
– phpricardo
@phpricardo without problems, is not a complaint, and mark as duplicate is no kind of reprobation or punishment. The idea is to help, but if you stay here, you’ll have to explain everything again to those who help.
– Bacco