ResultSet Empty but SQL Statement correct

Question

I'm trying to SELECT a column from my table, but the ResultSet is always empty. The SQL Statement works as expected when I try it in the IntelliJ database tool and I even printed it out using System.out.print and put it into the sql query tool and it works.

Because the ResultSet is empty I keep getting the error "ResultSet is not positioned correctly" (I tried writing rs.next but it didn't work)

Here is the code

private final String HELP_STATEMENT_STR =
                "SELECT a1atnr " +
                "FROM market.a1 INNER JOIN market.asol ON a1atnr = asoatnr " +
                "WHERE asoatikid = ?;";
    
private PreparedStatement HELP_STATEMENT;

public List<Long> deleteArticle(String ext_article_nr) throws SQLException {
    if (HELP_STATEMENT == null){
        HELP_STATEMENT = database.getConnection().prepareStatement(HELP_STATEMENT_STR);
    }
    HELP_STATEMENT.setString(1, ext_article_nr);
    System.out.println(HELP_STATEMENT.toString());
    ResultSet rs = HELP_STATEMENT.executeQuery();
    if (!rs.next()){
        System.out.println("ResultSet is empty");
    } else {
        System.out.println("ResultSet is not empty");
    }
    String nr = rs.getString("a1atnr");
}

I also tried using a normal Statement instead of a prepareStatement but the results are exactly the same

Any help would be appreciated

Answer 1

could you try adding aliases for the two tables in your FROM clause and use them in the ON clause like this:

"SELECT a1atnr " +
"FROM market.a1 a1Table INNER JOIN market.asol asolTable " +
"ON a1Table.a1atnr = asolTable.asoatnr " +
"WHERE asoatikid = ?;";