SQL statement with INNER JOIN to pull a column of two tables in Java

Question

SQL statement with INNER JOIN to pull a column of two tables in Java

Navigation

#1 by (1 votes)

1

I need help with a SQL statement. I have never worked with INNER JOIN and I can not do it correctly.

As you can see in the previous image, what I'm looking for is this:

Save the id_invitacion of the table invitacion

Replace id_invitado with nombre of table invitado , is say, instead of saving the id_invitado , I want to receive the String found in the column nombre

Replace id_evento with the table evento , that is, instead of saving the id_evento , I want to receive the String that is in the column nombre

To save the data, I have this code:

int i = 0;
do {
    registros[i][0] = rs.getInt("id_invitacion");
    registros[i][1] = rs.getString("nombreEvento");
    registros[i][2] = rs.getString("nombreInvitado");
    i++;
} while (rs.next());

So far this is the sentence that I created but, it does not work for me:

sSQL = "SELECT evento.nombre AS nombreEvento FROM evento "
                + ", invitado.nombre AS nombreInvitado FROM invitado "
                + "INNER JOIN invitacion ON evento.id_evento = invitacion.id_invitado "
                +"WHERE evento.nombre LIKE '%" + buscar + "%' ORDER BY id_invitacion";

Error I receive:

You have an error in your SQL syntax

Any help will be welcome.

mysql sql java join

asked by Robert Gomez 05.12.2016 в 04:40

source

1 answer

Android: Problems with AsyncTask when launching items PHP Slim Get POST parameters

score 1 · Accepted Answer

As you already suspected, the way you do joins in your SQL query is not valid. What's more, I do not see that you're returning the id_invitacion column. Here you have the corrected version (note: inner join and join are equivalent):

select invitacion.id_invitacion,
       evento.nombre as nombreEvento,
       invitado.nombre as nombreInvitado
  from evento
  join invitacion
    on invitacion.id_evento = evento.id_evento
  join invitado
    on invitado.id_invitado = invitacion.id_invitado
 where evento.nombre like '%buscar_cadena_aquí%'
 order by invitacion.id_invitacion

Additionally, you should not use a do-while loop, because by doing this, you will be trying to read record information before executing rs.next() , which is not legal and will give you an error. Rather, use a normal while loop:

int i = 0;

while (rs.next()) {
    registros[i][0] = rs.getInt("id_invitacion");
    registros[i][1] = rs.getString("nombreEvento");
    registros[i][2] = rs.getString("nombreInvitado");
    i++;
}