Using SQL Joins in JSP

[Tango524]

Hi- When I try to use a standard sql query SELECT FROM WHERE in my jsp code, I can pull data, but when I try to use a join in my code, there is a problem. I've used both the ansi join and the MS join in my code. My code has looked something like this...

SELECT Table1.Field1 FROM Table1 JOIN Table2 ON Table1.Field2=Table2.Field1 WHERE Table2.Field2 = '" + variable + "'

or

SELECT Table1.Field1 FROM Table1 , Table2 WHERE Table1.Field2=Table2.Field1 AND Table2.Field2 = '" + variable + "'

So I think it might be a problem with my Access DB relationships. I have the fields indexed and I have the tables linked and have tried the different relationship options. Does anyone have any suggestions?

Thanks!!

 

[sedj]

Can you perform the query in Access successfully ?

Are there errors, or do you just get nothing back ?

 

[Tango524]

Yes- I can perform this in Access sucessfully and I can perform the sql query in jsp using one table, but I have a problem when I try to join two tables to use data from both tables. There is no error. It just shows a blank screen on the browser and is done running.

Thanks!

 

[sedj]

I can think of no reason why the query should work in Access (via the SQL editor bit - yes ?) but no via JDBC - there should be no *difference*.

Are you sure that the variable you are passing via the JDBC call is definitely correct, and yields results ?

How are you retrieving results - via a ResultSet ?

 

[Tango524]

Yeah- It baffles me too! My result set is embedded in my table tags. It is like this below.

<%=rs.getString(&quot;Field1&quot%>

Thank you for your support.

Oh- and yes, my variables passes and everything works if my sql query only uses one table in the WHERE statement like this..

SELECT *
FROM TABLE1
WHERE FIELD1 = '&quot; + Variable + &quot;'

Thanks!

 

[sedj]

Instead of your rs.getString(&quot;Field1&quot try :

int columns = rs.getMetaData().getColumnCount();
while ( rs.next() ) {
  String tmp = "";
  for (int j = 1; j <= columns; j++) {
     tmp += rs.getString(j) +&quot; &quot;;
  }
  System.out.println(&quot;Data  - &quot; +tmp);
  out.println(&quot;Data  - &quot; +tmp);
}