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

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
keithedwardAsked:
Who is Participating?
 
Jim CakalicSenior Developer/ArchitectCommented:
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
 
pellepCommented:
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
 
pellepCommented:
sorry keith. misread your q.
0
 
pellepCommented:
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
 
keithedwardAuthor Commented:
THanks....

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.