Difficulty returning the result in a single query without duplicating

Asked

Viewed 116 times

2

I am trying to assemble a query (MYSQL) to return me all the clients that a particular seller can view. The rules to show the list of customers for this seller are: - Customers with contracts expired or without contracts. - Customers with bids expired or without bids. - Customers with existing contracts or offers still valid provided that they have been created by the consulting seller himself.

In short: I can only show customers available to this seller.

My scenario in the database is:

Estrutura das tabelas no meu BD

I arrived at the following query, but it is returning duplicate customers the problem happens by the fact that a customer may have been served by more than one seller, ie an expired offer from seller 1 and a valid contract by seller 2, in this case if seller 2 does the search, this customer appears twice in the result, because the Groupby clause has included both the registration linked to the seller of the table "Proposal" and the registration linked to the seller of the table "Contract".

View the query:

    select DISTINCT  `clientes`.`id`, `clientes`.`pessoa_tipo`, `clientes`.`razao_nome`, `clientes`.`nome_fantasia`, `clientes`.`email`, `clientes`.`logradouro`, `clientes`.`numero`, `clientes`.`complemento`, `clientes`.`bairro`, `clientes`.`cidade`, `clientes`.`uf`, `clientes`.`cep`, `clientes`.`telefone`, `clientes`.`celular`, `clientes`.`cnpj_cpf`, `clientes`.`inscricao_rg`, `clientes`.`contato_nome`, `clientes`.`contato_funcao`, `clientes`.`representante_nome`, `clientes`.`representante_rg`, `clientes`.`representante_cpf`, `clientes`.`atividade_rf`, `clientes`.`atividades_comerciais_id`, `contratos`.`vendedores_id` as `contrato_vendedor`, `propostas`.`vendedores_id` as `proposta_vendedor`
from `clientes` left join `contratos` on `contratos`.`clientes_id` = `clientes`.`id` left join `propostas` on `propostas`.`clientes_id` = `clientes`.`id` 
group by `clientes`.`id`, `clientes`.`pessoa_tipo`, `clientes`.`razao_nome`, `clientes`.`nome_fantasia`, `clientes`.`email`, `clientes`.`logradouro`, `clientes`.`numero`, `clientes`.`complemento`, `clientes`.`bairro`, `clientes`.`cidade`, `clientes`.`uf`, `clientes`.`cep`, `clientes`.`telefone`, `clientes`.`celular`, `clientes`.`cnpj_cpf`, `clientes`.`inscricao_rg`, `clientes`.`contato_nome`, `clientes`.`contato_funcao`, `clientes`.`representante_nome`, `clientes`.`representante_rg`, `clientes`.`representante_cpf`, `clientes`.`atividade_rf`, `clientes`.`atividades_comerciais_id`, `contratos`.`vendedores_id`, `propostas`.`vendedores_id` 
having (Max(contratos.vigencia_final) IS NULL AND Max(contratos.vigencia_final) IS NULL)
OR
(Max(contratos.vigencia_final) <= '2017-11-23' AND Max(propostas.validade) <= '2017-11-23')
OR
(Max(contratos.vigencia_final) <= '2017-11-23' AND Max(propostas.validade) IS NULL)
OR
(Max(propostas.validade) <= '2017-11-23' AND Max(contratos.vigencia_final) IS NULL)
OR
(contratos.vendedores_id = '3' OR propostas.vendedores_id = '3')

Resultado da query

mysql join group-by

  • What would be your ideal scenario? You want no bids, only contracts, if there is a valid contract for the customer?

  • I want to return customers without duplicating, in which I don’t need the seller ID in the results (which is outlined in blue, green and red)

  • So your problem will be solved if you remove contrato_vendedor and proposta_vendedor of your select and of group by, since you don’t need such data in the result. It duplicates because there is data that makes the record unique (sellers Ids).

  • but if I don’t include them in select I can’t use them in HAVING comparisons

  • truth, I have traveled! the @marconcilio-souza response makes sense in this case, but as there is comparison of equality in the case of sellers' ids, I think the best way out should be at the base of subselect: a select distinct [campos do cliente] from ([toda essa sua query]) as alias;

2 answers

0

you can remove these columns from group by and use the Max(sweat column) or Min(your spine).

Something like that.

select DISTINCT  `clientes`.`id`, `clientes`.`pessoa_tipo`, `clientes`.`razao_nome`, `clientes`.`nome_fantasia`, `clientes`.`email`, `clientes`.`logradouro`, `clientes`.`numero`, `clientes`.`complemento`, `clientes`.`bairro`, `clientes`.`cidade`, `clientes`.`uf`, `clientes`.`cep`, `clientes`.`telefone`, `clientes`.`celular`, `clientes`.`cnpj_cpf`, `clientes`.`inscricao_rg`, `clientes`.`contato_nome`, `clientes`.`contato_funcao`, `clientes`.`representante_nome`, `clientes`.`representante_rg`, `clientes`.`representante_cpf`, `clientes`.`atividade_rf`, `clientes`.`atividades_comerciais_id`, 

Max(`contratos`.`vendedores_id`) as`contrato_vendedor`, 

Max(`propostas`.`vendedores_id`) as `proposta_vendedor`

from `clientes` 
left join `contratos` 
on `contratos`.`clientes_id` = `clientes`.`id` 
left join `propostas` 
on `propostas`.`clientes_id` = `clientes`.`id` 
group by `clientes`.`id`, `clientes`.`pessoa_tipo`, `clientes`.`razao_nome`, `clientes`.`nome_fantasia`, `clientes`.`email`, `clientes`.`logradouro`, `clientes`.`numero`, `clientes`.`complemento`, `clientes`.`bairro`, `clientes`.`cidade`, `clientes`.`uf`, `clientes`.`cep`, `clientes`.`telefone`, `clientes`.`celular`, `clientes`.`cnpj_cpf`, `clientes`.`inscricao_rg`, `clientes`.`contato_nome`, `clientes`.`contato_funcao`, `clientes`.`representante_nome`, `clientes`.`representante_rg`, `clientes`.`representante_cpf`, `clientes`.`atividade_rf`, `clientes`.`atividades_comerciais_id`
having (Max(contratos.vigencia_final) IS NULL AND Max(contratos.vigencia_final) IS NULL)
OR
(Max(contratos.vigencia_final) <= '2017-11-23' AND Max(propostas.validade) <= '2017-11-23')
OR
(Max(contratos.vigencia_final) <= '2017-11-23' AND Max(propostas.validade) IS NULL)
OR
(Max(propostas.validade) <= '2017-11-23' AND Max(contratos.vigencia_final) IS NULL)
OR
(contratos.vendedores_id = '3' OR propostas.vendedores_id = '3')

Or as mentioned in the comments.

select * from
(
    select DISTINCT  `clientes`.`id`, `clientes`.`pessoa_tipo`, `clientes`.`razao_nome`, `clientes`.`nome_fantasia`, 
        `clientes`.`email`, `clientes`.`logradouro`, `clientes`.`numero`, `clientes`.`complemento`, `clientes`.`bairro`, 
        `clientes`.`cidade`, `clientes`.`uf`, `clientes`.`cep`, `clientes`.`telefone`, `clientes`.`celular`, `clientes`.`cnpj_cpf`, 
        `clientes`.`inscricao_rg`, `clientes`.`contato_nome`, `clientes`.`contato_funcao`, `clientes`.`representante_nome`, 
        `clientes`.`representante_rg`, `clientes`.`representante_cpf`, `clientes`.`atividade_rf`, `clientes`.`atividades_comerciais_id`, 
        `contratos`.`vendedores_id` as`contrato_vendedor`, `propostas`.`vendedores_id` as `proposta_vendedor`
    from `clientes` 
    left join `contratos` 
        on `contratos`.`clientes_id` = `clientes`.`id` 
    left join `propostas` 
        on `propostas`.`clientes_id` = `clientes`.`id` 
    group by `clientes`.`id`, `clientes`.`pessoa_tipo`, `clientes`.`razao_nome`, `clientes`.`nome_fantasia`, 
        `clientes`.`email`, `clientes`.`logradouro`, `clientes`.`numero`, `clientes`.`complemento`, `clientes`.`bairro`, 
        `clientes`.`cidade`, `clientes`.`uf`, `clientes`.`cep`, `clientes`.`telefone`, `clientes`.`celular`, `clientes`.`cnpj_cpf`, 
        `clientes`.`inscricao_rg`, `clientes`.`contato_nome`, `clientes`.`contato_funcao`, `clientes`.`representante_nome`, 
        `clientes`.`representante_rg`, `clientes`.`representante_cpf`, `clientes`.`atividade_rf`, `clientes`.`atividades_comerciais_id`,
        `contratos`.`vendedores_id`, `propostas`.`vendedores_id`
    having (Max(contratos.vigencia_final) IS NULL AND Max(contratos.vigencia_final) IS NULL)
    OR
    (Max(contratos.vigencia_final) <= '2017-11-23' AND Max(propostas.validade) <= '2017-11-23')
    OR
    (Max(contratos.vigencia_final) <= '2017-11-23' AND Max(propostas.validade) IS NULL)
    OR
    (Max(propostas.validade) <= '2017-11-23' AND Max(contratos.vigencia_final) IS NULL)
)
where contratos.vendedores_id = '3' 
OR propostas.vendedores_id = '3'

There you have to see what outcome you expect or need.

  • Returns Unknown column error

  • See which column is giving error, see if the name is correct... or if you need to use simple quotes with Max()

  • The error is this: Unknown column contracts.salesers_id in having clause. Since it is not in SELECT, I think this may be the problem

  • Actually he is, but he’s got an alias... Max(contratos.vendedores_id) ascontrato_vendedor,

  • then as I am using the MAX it will not return low ID ( if I have 3 and 8 as in the example... the 3 will be out)

  • I understand, in that case you need to remove from your having and use in a subselect .... just so you will achieve the expected result.

Show 1 more comment

0

You have to put some aggregator function for these columns like SUM() and take them out of group by, or simply take these columns out of your query, depending on what information you need that column to return.

  • I need to put them in SELECT because I need to use it in HAVING in order to get back to me customers who are served by sellers of such ID. If you do not put for example the field "contracts.vendes_id" mysql returns that this column is missing.

Browser other questions tagged mysql join group-by

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