Select from empty data

Asked

Viewed 52 times

0

My code returns all users, checking if they paid the last month, however I connect two tables, one of all customers with the registered payments table, I want to print on jTable even if you have no payment records, however left join seems to be playing the same role as inner join, follows the piece of code:

public void preencherTabela(){
    ArrayList dados = new ArrayList();
    String [] Colunas = new String[]{"CPF", "NOME", "DATA", "PAGO"};

    try{
        Connection con = Conexao.getConexao();
        Statement stmt = con.createStatement();
        ResultSet RS = stmt.executeQuery("Select cadastrarpac.nome, cadastrarpac.cpf, max(pagamento.data) from cadastrarpac "
                + "left join pagamento on (cadastrarpac.IdPac=pagamento.id_pac)");
        while(RS.next()){
            String nome = RS.getString("nome");
            String cpf = RS.getString("Cpf");
            String data = RS.getString("max(pagamento.data)");
            Date date = new SimpleDateFormat("dd/MM/yyyy").parse(data);  
            int data2 = date.getMonth()+1;
            SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
            Date now = new Date();
            int data3 = now.getMonth()+1;


            boolean pago;   
            if (data2==data3){
                pago=true;
            if (data.isEmpty()){
                pago=false;
            }
            }else{
                pago=false;
            }

            dados.add(new Object[]{cpf, nome, data, pago});
        }
    }catch(Exception e){

    }
    ModeloTabela modelo = new ModeloTabela(dados, Colunas);
    table.setModel(modelo);
    table.getColumnModel().getColumn(0).setPreferredWidth(80);
    table.getColumnModel().getColumn(0).setResizable(false);
    table.getColumnModel().getColumn(1).setPreferredWidth(176);
    table.getColumnModel().getColumn(1).setResizable(false);
    table.getColumnModel().getColumn(2).setPreferredWidth(75);
    table.getColumnModel().getColumn(2).setResizable(false);
    table.getColumnModel().getColumn(3).setPreferredWidth(80);
    table.getColumnModel().getColumn(3).setResizable(false);
    table.getTableHeader().setReorderingAllowed(false);
    table.setAutoResizeMode(table.AUTO_RESIZE_OFF);
    table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
}

Tabela pagamento:
id_pag int not null primary key, //id do registro de pagamento
id_pac int not null              //id do paciente(cliente)
data varchar(80) not null        //data do pagamento

insert into pagamento (id_pac, data) values (1, 20/04/2016)
insert into pagamento (id_pac, data) values (2, 21/04/2016)

Tabela cadastrarpac:
IdPac int not null primary key, //id do paciente 
Cpf varchar(12) not null,       //cpf 
Nome varchar(80) not null       //nome

insert into cadastrarpac (Cpf, Nome) values (088.758.960-0, Matheus) //id 1
insert into cadastrarpac (Cpf, Nome) values (089.858.966-0, Miriam)  //id 2

Any help is welcome, Thank you

java swing select join jtable

  • Experiemente withdraw the max of max(payment.date) and perform the consultation again.

  • If you do this no results come out, nor those who have record of payment

  • Very strange, your left table is the cadastrarpac, so you should return something if you don’t have it in the payment. Do another test remove parentheses and leave as follows: payment.id_pac = register.Idpa

  • It continued giving the same result, very strange even, because I’m using the left and not the Internet... was to bring unrelated results...

  • Edit the question and put the structure of the two tables with only 2 or 3 fields for me to test here.

  • How exactly do you want it? the code of the tables or just what they are formed?

  • I wanted the code and the data that forms it. But the code can be well summarized and the information too.

  • All right, that’ll do?

  • This max() is screwing everything up ....

  • No LEFT JOIN with aggregation. Try to get this maximum with a subquery. http://stackoverflow.com/questions/26928246/how-to-user-aggregate-function-with-left-join-mysql

  • Or maybe a UNION as a query that seeks customers without payment.

  • I tested here, the problem is in MAX, with it the query only returns 1 result, because the other has no record, so MAX returns null, already in the other record, has a value, then it is returned. Take out the MAX and you’ll get it

  • Without max, returned 2 results.

  • Is that I need the max to get the last recorded date by each user, as I could do it?

  • Make a registration without associating in payment, without max will continue to bring only those who have payment....

  • Well, it returns right null value? that’s exactly what I want, but I wanted it to return showing this null, there’s no way?

  • You will have to use a SUB query to do this. You cannot return the null

Show 12 more comments

2 answers

0


When one gives SELECT max(), the return will always be at most one registration/line.
For more lines to appear, you have to use one GROUP BY, or not to use the max().

  • Po, thanks mt, solved part of the problem, had forgotten the group by, now I will do some tests to get the expected result, thanks

0

Problem solved, such as the jTable does not print what is null, I have made it that every time I register a new patient he automatically had his first payment with a "null" date, in case "01/01/0001" may not be the most professional way, but it worked very well, thanks to all who were willing to help ;)

Browser other questions tagged java swing select join jtable

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