I have this issue lately about result sets in java using JDBC.
To make it easy to understand, I will illustrate an example with two functions A and B, so let's say we create a result set in function A we call it rs1, this rs1 will return to us the group of books that were borrowed from a library.
So with each book that was fetched from rs1 we call function B that will define another result set rs2 (rs2 was created using the same connection instance) to determine the person that has borrowed that book.
The problem here is that when we extract the first book from rs1 and call B after that rs1.next() returns false so it does not go past the first book but when I don't define rs2 in B it works perfectly. Why is that?
Note: rs1 and rs2 where created using the same connection instance!
Are you using the same Statement to execute both queries? JDBC only allows one open ResultSet for each Statement, so if you use the same Statement for the inner query then it will close the one you were using for the outer query. You should be able to do what you want if you use a separate Statement for the inner query.