• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Juliane,
What error message are you getting?
 
juliane gross
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The error message is:

 
juliane gross
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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);
 
reply
    Bookmark Topic Watch Topic
  • New Topic