Same column names in JOIN getting "column not found" error in ResultSet

Question

I have three tables firm, shipp and supplier.

all three columns have same column names as id, name, city ... other.

And I have written a join like in java:

String Query = "SELECT s.name Sname,s.city Scity,f.name Fname,f.city Fcity,"
        +"su.name Suname,su.city Sucity "
        +"FROM `order_details` ot "
        +"INNER JOIN `order` o ON ot.odr_id = o.odr_id " 
        +"INNER JOIN `product` p ON ot.pro_id = p.id "
        +"INNER JOIN `firm` f ON o.firm_id = f.id "
        +"INNER JOIN `shipp` s ON o.shipp_id = s.id "
        +"INNER JOIN `supplier` su ON o.sup_id = su.id ";

product,order and order_details are the other tables that are used in join.

This query working in phpMyAdmin.

But in java ResultSet giving "column 'Sname' not found." error.

I have tried below solutions but these are not working for me:

1) java.sq.SQLException: Column not found

2) Strange SQLException: Column not found

3) Many other but no one working

Error Stack:

java.sql.SQLException: Column 'Sname' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)

And:

SEVERE: null

Please help me!

Thanks In advance.

Edit 1:

Problem Reproduce:

When I run it with fetching data only from one aliased table (e.g. shipp) then it fetches data fine:

SELECT s.name Sname,s.city Scity ... other query part

But as soon as I SELECT columns from other aliased tables (e.g. shipp, firm, and supplier) then it throws error exception written above (Column not found).

SELECT s.name Sname,s.city Scity,f.name Fname,f.city Fcity,
       su.name Suname,su.city Sucity ... other query part

Note: This query running fine in phpmyadmin but not running with ResultSet fetch.

And this is how I'm getting columns from ResultSet (rs2):

String firstColumn = "";
String secondColumn = "";

while(rs2.next()) {
    firstColumn = "Shipp to : ";
    firstColumn += rs2.getString("Sname") + rs2.getString("Scity") ;
    firstColumn += "Person : " + rs2.getString("Fname") +"+ "+ rs2.getString("Fcity");

    secondColumn = "Supplier : " + rs2.getString("Suname");
    secondColumn += rs2.getString("Suname");
    secondColumn += rs2.getString("Sucity");
}

Can you provide [a complete example](https://stackoverflow.com/help/mcve) (with execution and reading the results)? Also, I wonder if you can reproduce this error with a simpler query (e.g. "select s.name Sname from `shipp` s") — default locale, Apr 25 '18 at 16:06
I couldn't reproduce this problem. Please, post the complete example: 1) create tables from scratch; 2) post the minimal, but complete, executable code example (include the code you're using to access the column in resultset). — default locale, Apr 25 '18 at 16:56
The equivalent code works fine on my machine :) Is this the complete query? Which version of mysql JDBC driver are you using to connect to database? — default locale, Apr 25 '18 at 17:32
Why are you joining tables you don't need?`+"INNER JOIN \`product\` p ON ot.pro_id = p.id "` your not using `p` in any other place than the very join your set up it's not needed stop adding unnecessary load to your database server's CPU. — Barkermn01, Apr 26 '18 at 16:26
@MartinBarker that's product tables are necessary to me in join. — , Apr 26 '18 at 17:02
not in that query, it's not. As I said you're not using `p` other than in the join statement's on. so your not using the product in that query. — Barkermn01, Apr 26 '18 at 17:21
regarding this error SEVERE: null have you tried to verify if the rs2 is null? — Christopher Pelayo, May 02 '18 at 02:52

score 3 · Accepted Answer · answered Apr 26 '18 at 12:37

Based on your error message I assume that you are using MySQL with the latest connector.

You can utilize the positional queries if necessary, this is using the number on which position on which the element could be find for example:

rs2.getString(1);

(Yes the numbering starts at 1...) It should be equivalent to this (of course without the capitalization problem):

rs2.getString("Sname");

This way you have to know the position only, but if you look at case sensitivity in the database/driver is pretty much the question of settings. To get around most of the problems I write my queries in capital if possible and without a backtick `.

To explore your ResultSet object try the following:

Statement statement = ...;
String queryString = "SELECT s.name sname,s.city scity,f.name fname, f.city fcity, su.name suname, su.city sucity "
        +" FROM `order_details` ot "
        +" INNER JOIN `order` o ON ot.odr_id = o.odr_id "
        +" INNER JOIN `product` p ON ot.pro_id = p.id "
        +" INNER JOIN `firm` f ON o.firm_id = f.id "
        +" INNER JOIN `shipp` s ON o.shipp_id = s.id "
        +" INNER JOIN `supplier` su ON o.sup_id = su.id ";
ResultSet resultSet = statement.executeQuery(queryString);
ResultSetMetaData metaData = resultSet.getMetaData();
int colCount = metaData.getColumnCount();
if (resultSet.next()) {
    for (int i = 1; i <= colCount; i++) {
        System.out.println("Col(" + i + ") '" + metaData.getColumnName(i) + "' value:" + resultSet.getString(i));
    }
}else{
   System.out.println("No row found.");
}

This snippet will print first results the columns and values which are returned by the driver you are using.

score 1 · Answer 2 · answered Apr 30 '18 at 10:49

1

Please check you have imported the correct jdbc connection package com.mysql.jdbc.connection. You could have imported java.sql.connection instead as here

answered Apr 30 '18 at 10:49

Vignesh

343
2
5
13

Same column names in JOIN getting "column not found" error in ResultSet

Edit 1:

2 Answers2