Solved

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

Posted on 2002-04-03
5
195 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
In this post we will learn different types of Android Layout and some basics of an Android App.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

729 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