Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
groupSum6 challenge 6 77
solarwind tftp server 2 45
Cipher Configuration on Apache HTTPD 4 53
Java DateChooser? 3 29
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…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

863 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

18 Experts available now in Live!

Get 1:1 Help Now