Solved

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

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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 how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now