Query Mysql confusing results

Asked

Viewed 157 times

1

I have to make a mysql query through a Java method. This is the sql I did to test if it works:

select * from servicos S, clientes C, motores M where C.nome = "Claudio Fernando Pires" group by ordemServico

Theoretically he should only bring the services whose client was "Claudio Fernando Pires" right? Correct me if I’m wrong.

Only that it brings all the services, even being gold customer as "Claudio Fernando Pires", the same happens with any other parameter of the customer or engine.

This is my Java method:

public List<Servico> pesquisarServicos(String nomeCliente, String endereco, String marcaMotor, String modeloMotor) {

    String sql = "SELECT * FROM clientes C, motores M, servicos S "
            + "WHERE  C.nome LIKE ? AND C.endereco  LIKE ? AND M.marcaMotor LIKE ? AND M.modeloMotor LIKE ?"
            + "GROUP BY ordemServico";

    ResultSet listaResultados = null;
    List<Servico> servicos = new ArrayList();


    try {
        PreparedStatement comando = BD.conection.prepareStatement(sql);
            comando.setString(1, nomeCliente+"%");
            comando.setString(2, "%"+endereco+"%");
            comando.setString(3, marcaMotor+"%");
            comando.setString(4, modeloMotor+"%");

        listaResultados = comando.executeQuery();
        Servico servico;

        while (listaResultados.next()) {
            servico = new Servico();
            servico.setCliente(ControleCliente.buscarCliente(listaResultados.getString("CPF")));
            servico.setDataServico(listaResultados.getString("dataServico"));
            servico.setDescricaoServico(listaResultados.getString("observacao"));
            servico.setFormaPagamento(Servico.getFormaPagamento(listaResultados.getInt("formaPagamento")));
            servico.setMotor(ControleMotor.buscarMotor(listaResultados.getString("numMotor")));
            servico.setOrdemServico(listaResultados.getString("ordemServico"));
            servico.setTipoSevico(Servico.getTipoServico(listaResultados.getInt("tipoServico")));
            servico.setValorServico(listaResultados.getDouble("valorServico"));
            servicos.add(servico);
        }
        listaResultados.close();
        comando.close();
        System.out.println("Lista Resultante="+servicos.size());
    } catch (SQLException excessaoSQL) {
        excessaoSQL.printStackTrace();
    }
    return servicos;
}

Presents the same problem in my Java written view.

Look at the result by filling the name filter: Com o filtro nome

If I change the Name filter: Com outro nome Any suggestions of what’s going on and how to make the search work, for both cases?

These are the records in the Services table:

inserir a descrição da imagem aqui

should bring the records relating to the client to whom I have informed some attribute:

inserir a descrição da imagem aqui

in this case I used an attribute from the Services table itself, but this is the idea.

java mysql

1 answer

2


Use JOIN in this case, more reliable for what you want to do:

SELECT s.atributo_servico1, s.atributo_servico2 FROM servicos s
JOIN clientes c ON c.codigo = s.cliente
WHERE c.nome LIKE "Claudio Fernando Pires"
GROUP BY s.ordem_servico;

If you need a JOIN with another table to redeem the values of that other table just put below the client’s JOIN and so on.

Tips

For standardization in the database the fields should be ordem_servico rather than ordemServico. It’s okay to declare a variable ordemServico in Java, but we hardly notice this in relational databases.

I also recommend instead of using * directly in SELECT you mention the table fields c.codigo, c.nome_cliente and so on for you to have greater control of the fields that will be returned.

  • It didn’t work out the way you suggested. I will try to explain it in another way: I have a service that has as key the customer’s Cpf, that relates them, the same for the engine, being the serial number the key. What I need to do and for example: search a service stating the customer’s name Join worked in not duplicate the records, what had been happening. But it’s the way it is up there. Like, what I’m trying to do would be possible by going from Java to mysql?

  • Already tried to group by CPF?

  • I have 4 record in the bank, it brought me 2 results both for: GROUP BY C.Cpf or GROUP BY S.clienteId. From what I understand, GROUP BY it restricts my search to 1 item of each key for GROUP BY that I informed right? In this case I can have more than one record for each client or engine, so restricting by ordem_servico would be enough for it not to duplicate my records as it happened previously.

  • These 4 records are all from one service, this? And you want to show only 1? And the grouping by service identifier, would be a good solution? And so add up the value of the service resulting in the total correctly.

  • No, they’re all distinct, they can have identical values, but the ordem_servico makes them different. What I want is to bring a service informing a feature either of the engine or of the customer. Can you understand?

  • You could put an image of how the result should look and edit the question?

  • Let’s go continue this discussion in chat.

  • Guys, Giancarlo helped me a lot, now I’ve reached the point where when I inform some attribute to do the filtering, the same does not return me any item,?

Show 3 more comments

Browser other questions tagged java mysql

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