Return of duplicated SQL results

Question

Return of duplicated SQL results

Asked 8 years, 4 months ago

Viewed 881 times

-3

I have the following SQL

SELECT 
    `inte`.`int_nome`, 
    `age`.*, 
    `con_at`.`con_nome` as consultora_que_atendeu, 
    `con_ag`.`con_nome` as consultora_que_agendou 
FROM (`agendamentos` as age) 
    JOIN `interessados` as inte ON `inte`.`int_cod`=`age`.`age_cod_interessado` 
    JOIN `consultoras` as con_at ON `con_at`.`con_cod`=`age`.`age_cod_consultora_atendido` 
    JOIN `consultoras` as con_ag ON `con_ag`.`con_cod`=`age`.`age_cod_consultora_agendado` 
WHERE `inte`.`int_nome` LIKE '%baill%' 
GROUP BY `age`.`age_cod`

However, at the time of listing, it searches all the records and prints wrong.

Even if I run the query suggested by our friend Victor:

SELECT DISTINCT
    `inte`.`int_nome`, 
    `age`.*, 
    `con_at`.`con_nome` as consultora_que_atendeu, 
    `con_ag`.`con_nome` as consultora_que_agendou 
FROM (`agendamentos` as age) 
    JOIN `interessados` as inte ON `inte`.`int_cod`=`age`.`age_cod_interessado` 
    JOIN `consultoras` as con_at ON `con_at`.`con_cod`=`age`.`age_cod_consultora_atendido` 
    JOIN `consultoras` as con_ag ON `con_ag`.`con_cod`=`age`.`age_cod_consultora_agendado` 
WHERE `inte`.`int_nome` LIKE '%baill%' 
GROUP BY `age`.`age_cod`

What I’m missing in the consultation?

1

Our dear SELECT DISTINCT wouldn’t solve the problem? And the other question is: the other tables no longer have entries from the same USER [As shown in age_cod, 1 and 3] ?

– William Aparecido Brandino

2016/08/18 at 14:24
Where are the duplicates? All the results are different...

– Jorge B.

2016/08/18 at 14:24
1

Looking at the age_cod of its image, we have 1, 3, 5, 6, 7 and 8. Which one is duplicated and why?

– Victor Stafusa

2016/08/18 at 14:39
I still don’t understand André. What is the problem after all?

– Jorge B.

2016/08/18 at 14:39
You do a search for all records containing "Baill", the result seems to be as expected. What do you intend to get?

– Jorge B.

2016/08/18 at 14:41
@Jorgeb. the problem is that when displaying, it lists all the records, changing only the name, because the idea is to do a search by name, and return only the result of that name in question, but the name is in another table.

– Sr. André Baill

2016/08/18 at 14:41
@But aren’t all those records from that user? It’s just that they seem to be.

– Jorge B.

2016/08/18 at 14:43
2

What you’re trying to get is a list of appointments, a list of interested parties, a list of consultants or a list of what after all? What is it that you call duplicate and what is it that you don’t call duplicate?

– Victor Stafusa

2016/08/18 at 14:45
The list I need is the list of schedules of that name in question

– Sr. André Baill

2016/08/18 at 14:47
But in my Where, I added: WHERE inte.int_nome LIKE '%baill%' AND age.age_cod_interessado = inte.int_cod seems to have resolved

– Sr. André Baill

2016/08/18 at 14:47
What do I do? Can you close the topic? Apparently you solved the issue using int.int_cod

– Sr. André Baill

2016/08/18 at 14:49
Post an answer with the solution.

– Fagner Fonseca

2016/08/18 at 16:23

Show 7 more comments

1 answer

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.

by Victor Stafusa • **63,338** points · Answer 1 · 2016-08-18T14:24:37+00:00

1

Just put a DISTINCT:

SELECT DISTINCT
    `inte`.`int_nome`, 
    `age`.*, 
    `con_at`.`con_nome` as consultora_que_atendeu, 
    `con_ag`.`con_nome` as consultora_que_agendou 
FROM (`agendamentos` as age) 
    JOIN `interessados` as inte ON `inte`.`int_cod`=`age`.`age_cod_interessado` 
    JOIN `consultoras` as con_at ON `con_at`.`con_cod`=`age`.`age_cod_consultora_atendido` 
    JOIN `consultoras` as con_ag ON `con_ag`.`con_cod`=`age`.`age_cod_consultora_agendado` 
WHERE `inte`.`int_nome` LIKE '%baill%' 
GROUP BY `age`.`age_cod`

But where are the duplicates?

– Jorge B.

2016/08/18 at 14:25
Sometimes I get the idea that people don’t even read the questions ;)

– Jorge B.

2016/08/18 at 14:26
@Jorgeb. I did read the question. He wants to delete duplicate records. Now, if his definition of "duplicate" is not what everyone else expects, then patience...

– Victor Stafusa

2016/08/18 at 14:27
but the results he shows are not duplicated, that’s all.

– Jorge B.

2016/08/18 at 14:29
@Jorgeb. Yes, the results he gave as an example are not duplicated, but if by chance they could be, just put the distinct in the query. On the other hand, all this debate, makes me wonder if the correct one would not simply vote to close this question as "it’s not clear what you’re asking".

– Victor Stafusa

2016/08/18 at 14:31
Actually, the DISTINCT would solve the duplication problem, but the results that are returning are different, because the 'age_cod' is different in the results.

– viana

2016/08/18 at 14:31
@Maybe that’s what Victorstafusa was. I’ve even done the courtesy :)

– Jorge B.

2016/08/18 at 14:35
@Cleidimarviana But, you know if the age_cod should or should not be taken into account to consider whether two data records should be considered duplicated or not? I think only the OP could say that. However, that GROUP BY suggests that it should be taken into account yes, and therefore that field would not be the problem.

– Victor Stafusa

2016/08/18 at 14:35
I changed my question, make it clearer.

– Sr. André Baill

2016/08/18 at 14:37
@Victorstafusa is not only that field that differs, the date is not the same. Anyway, this answer would never be valid with those question results. A DISTINCT would give the same results.

– Jorge B.

2016/08/18 at 14:37
@jorgeB, gave yes the same Results.

– Sr. André Baill

2016/08/18 at 14:38
I think if I group by name, maybe I would, but I don’t think it would be the right way to return the result.

– Sr. André Baill

2016/08/18 at 14:40
@Andrébaill but in doing so you would misrepresent the results, after all you do not want to search all the schedules of the 'baill''?

– Jorge B.

2016/08/18 at 14:44

Show 8 more comments