SQL INNER JOIN with three tables

2

I have a database about movies and I want to show the name of the genres a movie is assigned to.

Diagram (example values in red):

- EDITED -

This error comes out:

  

The method getString (String) is undefined for the type String

Code:

public DefaultTableModel tableRegistros() {
        DefaultTableModel model = null;

        sSQL = "SELECT PELICULAS.*, " 
                + "GENEROS.nombre AS nombreGenero, " 
                + "COMPANIAS.nombre AS nombreCompania " 
                + "FROM PELICULAS " 
                + "INNER JOIN COMPANIAS ON PELICULAS.id_compania = COMPANIAS.id_compania " 
                + "INNER JOIN PELICULAS_GENEROS ON PELICULAS.id_pelicula = PELICULAS_GENEROS.id_pelicula " 
                + "INNER JOIN GENEROS ON PELICULAS_GENEROS.id_genero = GENEROS.id_genero";

        // Java 7 try-with-resources
        try (Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery(sSQL)) {

            String [] titulosColumnas = {"ID", "Título", "Año", "Minutos", "Sinopsis", "Compañía", "Género"};
            ArrayList<Object[]> registros = new ArrayList<Object[]>();

            while (rs.next()) {
                registros.add(new Object[] {
                    rs.getInt("id_pelicula"),
                    rs.getString("titulo"),
                    rs.getInt("anoLanzamiento"),
                    rs.getInt("longitudMinutos"),
                    rs.getString("sinopsis"),
                    rs.getString("nombreCompania")
                    rs.getString("nombreGenero") // Error: The method getString(String) is undefined for the type String
                });
            }

            model = new DefaultTableModel(registros.toArray(new Object[0][]), titulosColumnas);

        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "SQLException:\n" + e, "Error: tableRegistros()", JOptionPane.ERROR_MESSAGE);
            }

        return model;
    }

The idea is:

  • Get the name of the company instead of the id_company.
  • Get all the names of the genera assigned to you movie.

What do I have wrong with the query ?. I'm a rookie with INNER JOIN.

Thanks in advance.

    
asked by Robert Gomez 13.11.2017 в 00:44
source

1 answer

3

Try this way:

"SELECT PELICULAS.*, " 
    + "GENEROS.nombre AS nombreGenero, " 
    + "COMPANIAS.nombre AS nombreCompania " 
    + "FROM PELICULAS " 
    + "INNER JOIN COMPANIAS ON PELICULAS.id_compania = COMPANIAS.id_compania " 
    + "INNER JOIN PELICULAS_GENEROS ON PELICULAS.id_pelicula = PELICULAS_GENEROS.id_pelicula " 
    + "INNER JOIN GENEROS ON PELICULAS_GENEROS.id_genero = GENEROS.id_genero"

Notes:

  • I have put the names of tables in uppercase, assuming that they are so, for the images.

  • When it's your turn to search for the titulo of the movie, you may have problems, I do not know if you noticed that you called titutlo to that column.

  • answered by 13.11.2017 / 01:23
    source