Solved

How do you reference col's in a join statement?

Posted on 2002-04-03
5
194 Views
Last Modified: 2010-03-31
Hello,

I have a join statement between two tables (actually it is a single table that has a recursive FK). How do  reference the col's in the rs.getString ?? Normally I just do a rs.getString("colname") but what if the columns are the same name? I would think if you have a staff.lname and a employee.lname, then you would ref it by rs.getString("staff.lname").... but it doesn't seem to work...

I can enter the col index value rs.getString(1) and it will return the right value, but I want to reference by the name of the col....


Thanks,
Keith
0
Comment
Question by:keithedward
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 4

Expert Comment

by:pellep
ID: 6915637
Same way as normal, I'd say. Since you still specify a field-list in the select clause even if you pile join statements afterwards.

select field1, field2, field3 from table1 join table2 on table1.field1=table2.field1

...
rs.getString(1) - field1
rs.getString(2) - field2 etc
Could post your SQL?
0
 
LVL 4

Expert Comment

by:pellep
ID: 6915645
sorry keith. misread your q.
0
 
LVL 19

Accepted Solution

by:
Jim Cakalic earned 50 total points
ID: 6915670
Alias the columns in the select like this:

SELECT s.lname as staff_lname, e.lname as employee_lname
FROM staff s, employee e
WHERE ...

Then, in your ResultSet processing, use the aliases to retrieve the columns by name:

rs.getString("staff_lname");
rs.getString("employee_lname");

Best regards,
Jim Cakalic
0
 
LVL 4

Expert Comment

by:pellep
ID: 6915681
You might be forced to use the ResultsetMetaData object. depending on the driver, the name of the table of the field can be extracted through getSchemaName(colIdx) or getTableName(colIdx) or getCatalogName(colIdx) (you'll have to experiment).



...
public int getColIdx(String tableName, String fieldName, ResultSetMetaData rsMeta) {
for (int i = 1 ; i <= rsMeta.getColumnCount() ; i++) {
if (rsMeta.getColumnName(i).equals(fieldName) && rsMeta.getTableName(i).equals(tableName)) {
return i;
}
}
return -1; //not found
}
ResultSetMetaData rsMeta = rs.getMetaData();
String val = rs.getString(getColIdx("table1", "field1", rsMeta);
etc.
0
 

Author Comment

by:keithedward
ID: 6915702
THanks....

0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
jsp insert to database example 2 86
Java: The Public Class Main 4 60
Java 8 to Java 6 8 41
junit as external jar or library 7 55
An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question