JOIN 3 tables +1 JDBC junction table

Question

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

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 associated dates 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:

I want to get this result:

Answer 1

Good, come on. Apparently there is no problem in your query.

Given the scenario exposed by you can only imagine that not all tables have the records to do JOIN, which would end up limiting 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 on the table function_has_query:

• since this relationship table was created, it means that you can have more than one professional related to a query?
• need to have the field of the user in this table, since it is present in the consultation and the primary key of the consultation is already present in this table?