wood burning stoves 2.0*
The moose likes JDBC and the fly likes Derby (Cloudscape): how to query 2 or more tables in one query-statement? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Derby (Cloudscape): how to query 2 or more tables in one query-statement?" Watch "Derby (Cloudscape): how to query 2 or more tables in one query-statement?" New topic
Author

Derby (Cloudscape): how to query 2 or more tables in one query-statement?

juliane gross
Ranch Hand

Joined: May 30, 2002
Posts: 161
Hi,
I am using Derby (cloudscape) embedded in a Swing-Application.

My question:
How can I process a query similar to this, where I have references
to 2 tables in the same query:

"SELECT Person.Author, Books.Title, Books.Year
FROM Person, Books
WHERE Person.ID = Books.ID";

It seems like Derby lets me connect to one table only at a time.
I searched the Manual and Reference script thoroughly but in vain.
I know that queries like the one I mentioned above are possible,
because I saw similar examples in the reference book, but they did not
show the according connection code.
I suspect the connection code must be different, but how?

Here is, how I query data from one table only, which works fine:

(My tables are within one Databasesystem (DBSytem.Books, DBSystem.Person etc.)).



Any help is very much appreciated, thank you!

Juliane

[ February 12, 2005: Message edited by: juliane gross ]
[ February 12, 2005: Message edited by: juliane gross ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30369
    
150

Juliane,
What error message are you getting?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
juliane gross
Ranch Hand

Joined: May 30, 2002
Posts: 161
The error message is:

juliane gross
Ranch Hand

Joined: May 30, 2002
Posts: 161
I understand the error message, since the getConnection() method never learns about the existence of table BOOK.
I probably need to adapt the getConnection syntax for multiple table queries, but don't know how.
Thank you for any tips!

Juliane
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30369
    
150

Juliane,
getConnection() just gets a connection. It isn't specific to the type of query.

I think there way be a typo in the query. In your SQL, you have "FROM Person, Books", but the error message has "the table 'BOOK' does not exist." One has an "s" at the end and one does not.

You can verify this by trying your single query example with the following two queries:
ResultSet rs = stm.executeQuery("SELECT title FROM Books);
ResultSet rs = stm.executeQuery("SELECT title FROM Book);
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Derby (Cloudscape): how to query 2 or more tables in one query-statement?