Home / Questions / 26982
Accepted
Bryan Romero
Asked: 2020-10-12 07:18:01 +0800 CST 2020-10-12 07:18:01 +0800 CST

Select values ​​from two different Java.Sql tables?

  • 772

Hello, I am trying to create a method that returns me String[]data from 2 different tables, in this case it is the table userand the table geolocation, the user data with their respective geolocation (city, neighborhood, latitude, longitude).

But I can't create a "correct" query.

static String[] getUserDataFromDb(JTextField id, JPasswordField pass) throws SQLException {

    String[] data = new String[9];

    Connection c = connectDB();

    Statement stmt = null;

    String query1 = "SELECT * FROM users INNER JOIN geolocation ON user.userid = geolocation.userid" ;

    stmt = c.createStatement();

    ResultSet rs = stmt.executeQuery(query1);

    while (rs.next()) {

        data[0] = rs.getString("userid").trim();
        data[1] = rs.getString("name").trim();
        data[2] = rs.getString("surname").trim();
        data[3] = rs.getString("email").trim();
        data[4] = rs.getString("password").trim();          
        data[5] = rs.getString("city").trim();
        data[6] = rs.getString("district").trim();
        data[7] = rs.getString("latitude").trim();
        data[8] = rs.getString("longitudes").trim();    

    }

    stmt.close();
    c.close();

    return data;

}

It always returns me a errorsince the query is not well written.

The tables are:

users:

+-------+-------------+------+-----+---------------------+
| name  | surname     |     email        |password|userid|   
+-------+-------------+------+-----+---------------------+
| jose  | aguilar     | [email protected] | 123    |jose23|
+-------+-------------+------+-----+---------------------+

geolocation:

    +-----------+-------------+-----------+---------+----------+
    | city      | district    | latitude  |longitude|   userid |
    +-------+-------------+-------------------------+----------+
    | su ciudad | su barrio   | 1.2       | -1.3    |   jose23 |
    +-----------+-------------+-----------+---------+----------+
java

2 Answers

  1. Best Answer

    Maybe it's because there are duplicate columns (userid) in the result. I always got that error when there were two columns with the same name in the query result.

    I guess when you want to do the

    data[0] = rs.getString("userid").trim();

    Java doesn't know which column you mean, since there are two

    Can you put the full error?

    EDITION:

    You have put user after the WHERE. It should be users. To avoid this careless error and as a good practice, you should use aliases

    SELECT * FROM users u INNER JOIN geolocation g ON u.userid = g.userid
    • 5

  2. I think you have misspelled the table name in the join:

    ON user.userid = geolocation.userid

    Should be:

    ON users.userid=...
    • 1