• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
juliane gross
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34656
366
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Juliane,
What error message are you getting?
 
juliane gross
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The error message is:

 
juliane gross
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34656
366
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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);
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic