JOIN of 3 tables +1 JDBC join table

Navigation
-1

I have a java program that returns the list of users who have a query on that day, so I built a database that lists some important data of the users, the name of the doctor with whom they will have a query and some such as the living room and the floor. For the SQL query to return only those queries that day I tried to JOIN the 3 tables + the join table with a WHERE so that it returns the queries that are scheduled for the day that is compared to the variable dataFinal (var from the Java program which gets the date in the format yyyy / MM / dd).

The problem is that this query is only returning a single user who has a query on that date, but there are more users who have the same dates associated and are not appearing.

Java: 

public void componentShown(ComponentEvent arg0) {

            DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
            Date date = new Date();
            System.out.println(dateFormat.format(date));

            String dataFinal = dateFormat.format(date);

            try {

                Connection lig = DriverManager.getConnection("jdbc:mysql://localhost/htmdb", "root", "");
                PreparedStatement inst = lig

                        .prepareStatement(
                                "SELECT nome, sala_piso, nomeFuncionario, departamento, data, tempo, confirmacao FROM consulta "
                                        + "JOIN utentes ON consulta.utentes_utente_id = utentes.utente_id "
                                        + "JOIN funcionarios_has_consulta ON funcionarios_has_consulta.consulta_consulta_id = consulta.consulta_id "
                                        + "JOIN funcionarios ON funcionarios.funcionario_id = funcionarios_has_consulta.funcionarios_funcionario_id WHERE data ='"
                                        + dataFinal + "'  ");


                ResultSet rs = inst.executeQuery();

                tableAgendadas.setModel(DbUtils.resultSetToTableModel(rs));

                JOptionPane.showMessageDialog(frmHealthTreatmentManager, "Hoje é dia : " + dataFinal);

                lig.close();

            } catch (SQLException e1) {
                JOptionPane.showMessageDialog(frmHealthTreatmentManager,
                        "Impossível ligar à base de dados. " + e1.getLocalizedMessage());

            }

        }

Database:

Iwanttogetthisresult:

    
asked by anonymous 17.06.2016 / 12:28

1 answer

0

Well, come on. Apparently there is no problem with your query.

Given the scenario exposed by you, I can only imagine that not all tables have the records to do the JOIN, which would limit the return of the query.

So I changed the query to use LEFT JOIN : 

SELECT u.nome, 
       c.sala_piso,
       f.nomeFuncionario,
       f.departamento,
       c.data,
       c.tempo,
       c.confirmacao
  FROM consulta c
 INNER JOIN utentes u ON c.utentes_utente_id = u.utente_id 
  LEFT JOIN funcionarios_has_consulta fhc ON c.consulta_id = fhc.consulta_consulta_id
  LEFT JOIN funcionarios f ON fhc.funcionarios_funcionario_id f.funcionario_id
 WHERE c.data = '2016-06-17';

Some comments about the official_query_tab table:

  • Since this relationship table was created, it means that you can have more than one professional related to a query?
  • Is there a need to have the user field in this table, since it is present in the query and the primary query key is already present in this table?
17.06.2016 / 14:03
Laravel does not return variable passed by with How to update a User entity that is converted to application?